|
I work in VB.Net, so maybe this is a language difference, but why aren't you filling the datarow by using column names instead of an index? I would guess that the problem is that you are filling a column with an incorrect datatype or something like that. If you used column names instead of indexes, you could insure this would not happen. For example:
dr("SyleColumn") = "bold";
instead of
dr[11] = "bold";
But again, this may not be the exact syntax to do it in C#.
HOpe this helps.
|
|
|
|
|
Hi frndz,
In my machine, I have a MSSQL server named "Local" with one database "TEST1" and in server machine there is also one MSSQL server named "Sever" with one database named "TEST" with table "Tbl"
My doubt is,is it possible to excute
"select * from TEST.Tbl" query
from query analyser of "local"'.
Thanks in advance
All I ever wanted is what others have....
CrazySanker
|
|
|
|
|
If you set up a linked server, you should be able to query databases on this server using the syntax server.database.owner.table
Paul Marfleet
|
|
|
|
|
Thank u for your reply.
I setup the linked server command like
<br />
EXEC sp_addlinkedserver <br />
<br />
'server name',<br />
<br />
N'SQL Server'<br />
<br />
select * from [server name].[TEST].[dbo].[Tbl]<br />
but it shows error like
<br />
Server: Msg 18452, Level 14, State 1, Line 7<br />
Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.<br />
All I ever wanted is what others have....
CrazySanker
|
|
|
|
|
If you are adding the linked server dynamically in code, you should call the sp_addlinkedsrvlogin SP to set up the login credentials required to access the linked server. Specify a SQL Server login if you can't access the linked server using a trusted connection.
If the server you are connecting to isn't an instance of SQL Server, you can use the OPENQUERY function to execute a pass-through query against the server.
Paul Marfleet
|
|
|
|
|
Hi pmarfleet ,
Thanks for your reply.Now I got the solution.
I add this as linked server through Enterprise manager->security->linked server
when i tried this "exec sp_addlinkedserver" SP,it shows some problem.
anyway now its working fine..
Thank you once again for spending time for me!!!
All I ever wanted is what others have....
CrazySanker
|
|
|
|
|
through my db i want send e-mails periodically,but i don't know to perform this task periodically . how can i do this ?
|
|
|
|
|
Using db you can not send the mail periodically. But this method will help you.
Create an excutable file which contains sending mail code.
Schedule this excutable file using windows scheduler from control panel.
Now your executable file will check the pending mails from your db and will send the mail. Once mail is sent then set the flag sent so that it will not sent again.
|
|
|
|
|
Have you considered using SQL Server Agent?
|
|
|
|
|
Hi experts,
I've installed Microsoft SQL Server 2005 (Developers edition, not the Express edition) in my machine and what I want to do is create a database there, just including few tables. From where I should start it, I've never use SQL before.
Here are the steps what I've tried after installation.
1.) First open the "Microsoft SQL Server Management Studio".
2.) Then click "File" -> "Connect Object Explorer..."
3.) On the "Connect to Server" dialog box, for "Server type" gives the "Database Engine", and for "Server name" gives the my computer name.
4.) Then click the "Connect" button, but no luck.
I got the following error.
<br />
An error has occurred while establishing a connection to the server. When connecting to SQL server<br />
2005, this failure may be caused by the fact that under the default settings SQL Server dose not<br />
allow remote connections.(provider: Named Pipes Provider, error:40-Could not open a connection<br />
to SQL Server)(Microsoft SQL Server,Error:2)<br />
I appreciate your help all the time...
Eranga
|
|
|
|
|
How should shame myself. Everything is done on WinXP, and SQL 2005 Enterprise Edition on support on it. So I've to use Express Edition. I'm really sorry for waisting your guys time reading my post.
I appreciate your help all the time...
Eranga
|
|
|
|
|
Hi! I know it's possible to have a data-driven subscription in Reporting Services, but I'm looking for a way to have a data-driven schedule. Every month I send out some reports, but can only have these sent out once other data has been updated. Is there any way that I can trigger the subscription schedule based on queried data? Thanks!
|
|
|
|
|
Anyone pls help me!! i am newbie. i got query below, i want the result as NF 20000.00 if it is refinanceAmount. i know below is not a proper query
IF L.LoanID EXISTS IN (SELECT LoanID, RefinanceAmount FROM FastLoan_Applications.dbo.tblLoanPurpose)
SET @LoanAmount = 'N.F.'@RefinanceAmount
Here is the Main SP
set ANSI_NULLS OFF
set QUOTED_IDENTIFIER OFF
GO
ALTER PROC [dbo].[upUpfront_GetPending]
@UserID varchar(10)
AS
BEGIN
--ASSERTION: If a weekly and a monthly upfront run is in pending simultaneously, this SP will only return the weekly upfront.
--To get monthly upfront, weekly must be processed first.
--GET CURRENT UPFRONT COMMISSION RUN (WEEKLY OR MONTHLY, BUT ONLY ONE)
DECLARE @CommissionRunID int
SELECT @CommissionRunID = CommissionRunID
FROM tblCommissionRun
WHERE CommissionRunTypeID IN (1,2)
AND Pending = 1
--UPFRONT LOANS
SELECT U.UpfrontID,
AG.IntroducerID [AggregatorID],
AG.TradingName [Aggregator],
I.IntroducerID,
I.TradingName,
U.LoanID,
'' [LoanIncrease], --tooltip
A.ShortName,
LS12.StageDate [SettlementDate],
L.LoanAmount [Amount],
U.UpfrontRate,
U.UpfrontAmount,
U.ApplicationFee,
U.UpfrontGST,
U.UpfrontTotal
FROM tblUpfront U
INNER JOIN FastLoan_Applications.dbo.vwIntroducer I ON I.IntroducerID = U.IntroducerID
INNER JOIN FastLoan_Applications.dbo.tblLoan L ON L.LoanID = U.LoanID
INNER JOIN FastLoan_Applications.dbo.tblApplication A ON A.ApplicationID = L.ApplicationID
LEFT JOIN FastLoan_Applications.dbo.tblLoanStage LS12 ON LS12.LoanID = L.LoanID
LEFT JOIN FastLoan_Applications.dbo.vwIntroducer AG ON AG.IntroducerID = I.Aggregator
IF L.LoanID EXISTS IN (SELECT LoanID, RefinanceAmount FROM FastLoan_Applications.dbo.tblLoanPurpose)
SET @LoanAmount = 'N.F.'@RefinanceAmount
WHERE U.CommissionRunID = @CommissionRunID
AND LS12.LoanStageTypeID = 6
AND U.LoanIncreaseID IS NULL
UNION ALL
--UPFRONT LOAN INCREASES
SELECT U.UpfrontID,
AG.IntroducerID [AggregatorID],
AG.TradingName [Aggregator],
I.IntroducerID,
I.TradingName,
U.LoanID,
'Loan Increase' [LoanIncrease], --tooltip
A.ShortName,
LIS12.StageDate [SettlementDate],
LI.IncreaseAmount [Amount],
U.UpfrontRate,
U.UpfrontAmount,
U.ApplicationFee,
U.UpfrontGST,
U.UpfrontTotal
FROM tblUpfront U
INNER JOIN FastLoan_Applications.dbo.vwIntroducer I ON I.IntroducerID = U.IntroducerID
INNER JOIN FastLoan_Applications.dbo.tblLoanIncrease LI ON LI.LoanIncreaseID = U.LoanIncreaseID
INNER JOIN FastLoan_Applications.dbo.tblLoan L ON L.LoanID = LI.LoanID
INNER JOIN FastLoan_Applications.dbo.tblLoanPurpose LP ON LP.LoanID = L.LoanID
INNER JOIN FastLoan_Applications.dbo.tblApplication A ON A.ApplicationID = L.ApplicationID
LEFT JOIN FastLoan_Applications.dbo.tblLoanIncreaseStage LIS12 ON LIS12.LoanIncreaseID = LI.LoanIncreaseID
LEFT JOIN FastLoan_Applications.dbo.vwIntroducer AG ON AG.IntroducerID = I.Aggregator
WHERE U.CommissionRunID = @CommissionRunID
AND LIS12.LoanStageTypeID = 6
AND U.LoanIncreaseID IS NOT NULL
ORDER BY I.TradingName
END
Thanks in Advance.
|
|
|
|
|
Your SP does not declare a @RefinanceAmount variable, you cannot use it. Should it be a variable? Or maybe it is a column?
|
|
|
|
|
its not column, its a variable, i need to select it(@refinanceAmount) if, the loanId is present in Refinance(table). i need select the @RefinanceAmount but need to prefix or suffix characters i.e 'NF.Amt' $10000.00
Thanks for your concern
|
|
|
|
|
hi frndz,
I am using MSSQL 2000
I have 2 tables
Tbl1
ID Name
1 AAA
2 BBB
3 CCC
4 DDD
Tbl2
ID parentid
1 0
2 0
I need to select the values in tbl1 and not in tbl2
ie
ID NAme
3 CCC
4 DDD
how it possible??
All I ever wanted is what others have....
CrazySanker
|
|
|
|
|
i got one solution using "NOT IN"
i would like to know any other method..
SELECT ID from tbl1 where ID not in<br />
(SELECT ID from tbl2)
All I ever wanted is what others have....
CrazySanker
|
|
|
|
|
<br />
select * <br />
from Tbl1<br />
where Tbl1.ID not in<br />
(select ID from Tbl2)<br />
or
<br />
select t1.* <br />
from Tbl1 t1<br />
left join Tbl2 t2<br />
on t1.ID = t2.ID<br />
where t2.ID is null<br />
-- modified at 10:54 Monday 22nd October, 2007
You always pass failure on the way to success.
|
|
|
|
|
Hi all,
Table schema
empid primary key
emp_name
emp_age
emp_skills
emp_Isactive
I am storing multiple skills of a customer in same column separated with commas.I have designed a search form where in the employer enters skills in search box separated using commas. I want to match these skills irrespective of commas.
for this purpose i used a stored proc and passed skills as parameters
select skill from tbljob where skill like '%'+ISNULL(@skill1,skill)+'%' or skill like '%'+ISNULL(@skill2,skill)+'%' or skill like '%'+ISNULL(@skill3,skill)+'%'
but if one of the skill 1,2,3 is null ie if user enters only 1 or 2 , it returns all rows because the other condition is always true .
How can i use "OR" with other statements so that it works out
|
|
|
|
|
change your ISNULL() NULL result to something that can't be located:
ISNULL(@skill1,'&^%$')
|
|
|
|
|
Hi im executing a sql command from my C# windows form.
Its a count command : SELECT [COLUMN NAME], COUNT(*) FROM [DATABASE] GROUP BY [COLUMN].
2 Columns load from the results, the column name, and the number of records (count).
How can i display these results in a C# windows form? Datagrid, large textbox?
Please help me on this.
|
|
|
|
|
You can display them pretty much whichever way you want. What are the requirements? Grid seems like a good idea to me.
|
|
|
|
|
Hi,
How to convert .csv file .mdb file?
I am working in PL/SQL (toad) and C#
Here is my sample code:
string strConn = "Data Source=;User ID=; Password=;";
OracleConnection conn = new OracleConnection(strConn);
conn.Open();
OracleCommand cmd = new OracleCommand();
string strSQL;
strSQL = "";
strSQL += "INSERT INTO [MY_TBL] (";
strSQL += "F_NAME, ";
strSQL += "L_NAME) VALUES ( ";
strSQL += "SELECT ";
strSQL += "F_NAME, ";
strSQL += "L_NAME ";
strSQL += "FROM ";
strSQL += @"[Text;DATABASE=C:\myPath;]";
strSQL += ".";
strSQL += "[poc.csv])";
cmd = new OracleCommand(strSQL,conn);
cmd.ExecuteNonQuery();
conn.Close();
I am getting the error: invalid table name.
If any of you worked on this, can you please share the code with me.
shyam
|
|
|
|
|
The general method would be to import the text file (csv) into Access (into a temporary table), then manipulate the data from the temporary table into the correct locations using queries/code etc.
-------------------------------------------
Don't walk in front of me, I may not follow;
Don't walk behind me, I may not lead;
Just bugger off and leave me alone!!
|
|
|
|
|
Hi everyone.
I'm making a custom tool for registering working hours for my employer against a Visual FoxPro database.
I've created a procedure for executing sql queries against the database, but my insert statments aren't commited.
The return value (affected rows) says that the query was successfull.
I'm using a normal "insert into (col1, col2, col(n)) VALUES(val1, val2, val(n))" statement - so nothing fancy.
I've tried using transactions and myTransaction.Commit(); without success.
I've also tried using keywords like "GO" or "COMMIT" in the query - but the FoxPro ODBC driver isn't too keen on those keywords.
Am I missing something here?
I thought ODBCCommand.ExecuteNonQuery() used the auto-commit property.
Is there and specific properties that must be set, or keywords I must include to make it commit?
Sidenote:
If I copy/paste the query into an ODBC tool (freeware tool I just downloaded for testing purpouses) and execute the query,
the row is added successfully.
I'd really appreciate any feedback you've got.
-Larantz-
-- modified at 5:18 Monday 22nd October, 2007
I copied the database into a cvs folderstructure the other day.
Forgot to update the configuration file with the connectionstring the very next day...
*humble Larantz signing out*
-Larantz-
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
