Database Discussion Boards

Re: How Can i keep an Instance of SQL 2000, while Upgrading to 2005

Vimalsoft(Pty) Ltd1-Nov-07 0:26

1-Nov-07 0:26

Hi Paul

Thanks for the reply. So if i have Written my stored procedures in SQl2000, will they get Affected in 2005?

Thanks

Vuyiswa Maseko,

Sorrow is Better than Laughter, it may Sudden your Face, but It sharpens your Understanding

VB.NET/SQL7/2000/2005
http://vuyiswamb.007ihost.com
http://Ecadre.007ihost.com
vuyiswam@tshwane.gov.za

Re: How Can i keep an Instance of SQL 2000, while Upgrading to 2005

pmarfleet1-Nov-07 0:30

pmarfleet

1-Nov-07 0:30

Shouldn't be any problem. SQL 2005 is backwards-compatible, so all your stored procedure code should work OK.

Paul Marfleet

"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush

Re: How Can i keep an Instance of SQL 2000, while Upgrading to 2005

Vimalsoft(Pty) Ltd1-Nov-07 1:49

Vimalsoft(Pty) Ltd

1-Nov-07 1:49

Thanks
i Appreciate your help, last Question

If i have 2 servers one Running SQl2005 And the Other Running SQL 2000, will it be Possible to Access the a certain database in SQL2005 server from SQl 2000 server?

Thanks

Re: How Can i keep an Instance of SQL 2000, while Upgrading to 2005

pmarfleet1-Nov-07 1:53

pmarfleet

1-Nov-07 1:53

I'm not sure what you mean? How do you want to access it?

The best way to find out would be to try it for yourself. I don't have access to an instance of SQL 2000 at the moment.

Paul Marfleet

"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush

Iterate through Table Rows in sql

Mr.Sam31-Oct-07 21:40

Mr.Sam

31-Oct-07 21:40

Is it possible to iterate through a table or a temporary, because i have to add a temporary column and insert different values for each row according to condition to return a comma separated value. I show a rough sketch what i want to do.

Select SAP.LoginName,var="--Some Very Long Query according to a criteria within the rows of a table like where Test.Folder_ID=CM.Folder_ID--"
From dbo.sysCommunicatorMessages CM
Inner Join dbo.sysAppLogins SAP On CM.MESSAGEFROM = SAP.LOGIN_ID
where "--Some Criteria--" and CM.Folder_ID<>-2

I have used functions to solve the problem but they are currently not acceptable.

Bye

Re: Iterate through Table Rows in sql

pmarfleet1-Nov-07 0:15

pmarfleet

1-Nov-07 0:15

You could use a CURSOR. Have a look at this article[^].

Paul Marfleet

"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush

creating ms access database dynamically

sindhutiwari31-Oct-07 20:40

sindhutiwari

31-Oct-07 20:40

hi
My aim is to create ms access database through c# dynamically
if any one knows or have any idea abt it please let me know

regards
sindhu tiwari

Always Innovative

Re: creating ms access database dynamically

pmarfleet1-Nov-07 0:21

pmarfleet

1-Nov-07 0:21

Did you try searching Google? I tried and found this[^].

Paul Marfleet

"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush

Re: creating ms access database dynamically

sindhutiwari1-Nov-07 1:14

sindhutiwari

1-Nov-07 1:14

any idea abt creating a table and its fields dynamically...........................................
regards
sindhu tiwari

its me sid

Re: creating ms access database dynamically

pmarfleet1-Nov-07 1:28

pmarfleet

1-Nov-07 1:28

Use the CREATE TABLE statement.

Paul Marfleet

"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush

Re: creating ms access database dynamically

sindhutiwari1-Nov-07 1:32

sindhutiwari

1-Nov-07 1:32

thanks for ur valuable,meaningful and expertise reply................................................
regards
sindhu tiwari

its me sid

Implicit conversion varchar to money

Giri K31-Oct-07 14:48

Giri K

31-Oct-07 14:48

need help Guys.,

how can i convert money to varchar, to add some prefix. having problem with the below statement.

Case When Isnull(LP.RefinanceAmount, '')='' Then L.LoanAmount Else Convert(money, 'N.F '+ LP.RefinanceAmount) END[Amount],

The Complete Sproc is

USE FastLoan_Commissions
GO
set ANSI_NULLS OFF
set QUOTED_IDENTIFIER ON
GO

ALTER PROC [dbo].[upExceptions_GetPending]<br />
@UserID	varchar(10)<br />
AS<br />
BEGIN<br />
<br />
	SELECT	E.ExceptionID,<br />
			I.IntroducerID,<br />
			I.TradingName,<br />
			E.LoanID,<br />
			'' [LoanIncrease], --tooltip<br />
			A.ShortName,<br />
			LS12.StageDate [SettlementDate],<br />
			CRT.CommissionRunType,<br />
			CR.CommissionRunDate,<br />
			ET.ExceptionType [Reason],<br />
			Case When Isnull(LP.RefinanceAmount, '')='' Then L.LoanAmount Else Convert(money, 'N.F '+ LP.RefinanceAmount) END[Amount],<br />
			ISNULL(L.CurrentBalance, 0) [CurrentBalance],<br />
			E.ExceptionRate,<br />
			NULL [ExceptionDays],<br />
			E.ExceptionAmount,<br />
			NULLIF(E.ExceptionGST, 0) [ExceptionGST],<br />
			E.ExceptionTotal<br />
	FROM		tblCommissionRun CR<br />
	INNER JOIN 	tblException E ON E.CommissionRunID = CR.CommissionRunID<br />
	INNER JOIN	FastLoan_Applications.dbo.tblIntroducer I ON I.IntroducerID = E.IntroducerID<br />
	INNER JOIN	FastLoan_Applications.dbo.tblLoan L ON L.LoanID = E.LoanID<br />
	INNER JOIN  FastLoan_Applications.dbo.tblLoanPurpose LP ON LP.LoanID = E.LoanID AND LP.RefinanceReasonID IN (8,9,12)<br />
	INNER JOIN	FastLoan_Applications.dbo.tblApplication A ON A.ApplicationID = L.ApplicationID<br />
	LEFT JOIN	FastLoan_Applications.dbo.tblLoanStage LS12 ON LS12.LoanID = L.LoanID<br />
		AND	LS12.LoanStageTypeID = 6<br />
	LEFT JOIN	trefCommissionRunType CRT ON CRT.CommissionRunTypeID = CR.CommissionRunTypeID<br />
	LEFT JOIN	trefExceptionType ET ON ET.ExceptionTypeID = E.ExceptionTypeID<br />
	WHERE	CR.Pending = 1<br />
		AND	CR.CommissionRunTypeID IN (1,2)<br />
		AND	E.LoanIncreaseID IS NULL<br />
	<br />
	UNION ALL<br />
<br />
	--UPFRONT LOAN INCREASES<br />
	SELECT	E.ExceptionID,<br />
			I.IntroducerID,<br />
			I.TradingName,<br />
			E.LoanID,<br />
			'Loan Increase' [LoanIncrease], --tooltip<br />
			A.ShortName,<br />
			LIS12.StageDate [SettlementDate],<br />
			CRT.CommissionRunType,<br />
			CR.CommissionRunDate,<br />
			ET.ExceptionType [Reason],<br />
			LI.IncreaseAmount [Amount],<br />
			ISNULL(L.CurrentBalance, 0) [CurrentBalance],<br />
			E.ExceptionRate,<br />
			NULL [ExceptionDays],<br />
			E.ExceptionAmount,<br />
			NULLIF(E.ExceptionGST, 0) [ExceptionGST],<br />
			E.ExceptionTotal<br />
	FROM		tblCommissionRun CR<br />
	INNER JOIN 	tblException E ON E.CommissionRunID = CR.CommissionRunID<br />
	INNER JOIN	FastLoan_Applications.dbo.tblIntroducer I ON I.IntroducerID = E.IntroducerID<br />
	INNER JOIN	FastLoan_Applications.dbo.tblLoan L ON L.LoanID = E.LoanID<br />
	INNER JOIN	FastLoan_Applications.dbo.tblLoanIncrease LI ON LI.LoanID = E.LoanID<br />
	INNER JOIN	FastLoan_Applications.dbo.tblApplication A ON A.ApplicationID = L.ApplicationID<br />
	LEFT JOIN	FastLoan_Applications.dbo.tblLoanIncreaseStage LIS12 ON LIS12.LoanIncreaseID = LI.LoanIncreaseID<br />
		AND	LIS12.LoanStageTypeID = 6<br />
	LEFT JOIN	trefCommissionRunType CRT ON CRT.CommissionRunTypeID = CR.CommissionRunTypeID<br />
	LEFT JOIN	trefExceptionType ET ON ET.ExceptionTypeID = E.ExceptionTypeID<br />
	WHERE	CR.Pending = 1<br />
		AND	CR.CommissionRunTypeID IN (1,2)<br />
		AND	E.LoanIncreaseID IS NOT NULL<br />
		<br />
<br />
	UNION ALL<br />
<br />
	--TRAIL LOANS<br />
	SELECT DISTINCT E.ExceptionID,<br />
			I.IntroducerID,<br />
			I.TradingName,<br />
			E.LoanID,<br />
			'' [LoanIncrease], --tooltip<br />
			A.ShortName,<br />
			LS12.StageDate [SettlementDate],<br />
			CRT.CommissionRunType,<br />
			CR.CommissionRunDate,<br />
			ET.ExceptionType [Reason],<br />
			Case When Isnull(LP.RefinanceAmount, '')='' Then L.LoanAmount Else Convert(money, 'N.F '+ LP.RefinanceAmount) END [Amount],<br />
			ISNULL(L.CurrentBalance, 0) [CurrentBalance],<br />
			E.ExceptionRate,<br />
			E.ExceptionDays,<br />
			E.ExceptionAmount,<br />
			NULLIF(E.ExceptionGST, 0) [ExceptionGST],<br />
			E.ExceptionTotal<br />
	FROM		tblCommissionRun CR<br />
	INNER JOIN 	tblException E ON E.CommissionRunID = CR.CommissionRunID<br />
	INNER JOIN	FastLoan_Applications.dbo.tblIntroducer I ON I.IntroducerID = E.IntroducerID<br />
	INNER JOIN	FastLoan_Applications.dbo.tblLoan L ON L.LoanID = E.LoanID<br />
	INNER JOIN  FastLoan_Applications.dbo.tblLoanPurpose LP ON LP.LoanID = E.LoanID AND LP.RefinanceReasonID IN (8,9,12)<br />
	INNER JOIN	FastLoan_Applications.dbo.tblApplication A ON A.ApplicationID = L.ApplicationID<br />
	LEFT JOIN	FastLoan_Applications.dbo.trelCustomerApplication CA ON CA.ApplicationID = L.ApplicationID<br />
	LEFT JOIN	FastLoan_Applications.dbo.tblCustomer C ON C.CustomerID = CA.CustomerID<br />
	LEFT JOIN	FastLoan_Applications.dbo.tblLoanStage LS12 ON LS12.LoanID = L.LoanID<br />
		AND	LS12.LoanStageTypeID = 6<br />
	LEFT JOIN	trefCommissionRunType CRT ON CRT.CommissionRunTypeID = CR.CommissionRunTypeID<br />
	LEFT JOIN	trefExceptionType ET ON ET.ExceptionTypeID = E.ExceptionTypeID<br />
	WHERE	CR.Pending = 1<br />
		AND	CR.CommissionRunTypeID = 3<br />
		AND	E.LoanIncreaseID IS NULL<br />
		AND	CA.ApplicantTypeID = 1<br />
<br />
	UNION ALL<br />
<br />
	--TRAIL LOAN INCREASES<br />
	SELECT DISTINCT E.ExceptionID,<br />
			I.IntroducerID,<br />
			I.TradingName,<br />
			E.LoanID,<br />
			'Loan Increase' [LoanIncrease], --tooltip<br />
			A.ShortName,<br />
			LIS12.StageDate [SettlementDate],<br />
			CRT.CommissionRunType,<br />
			CR.CommissionRunDate,<br />
			ET.ExceptionType [Reason],<br />
			LI.IncreaseAmount [Amount],<br />
			ISNULL(L.CurrentBalance, 0) [CurrentBalance],<br />
			E.ExceptionRate,<br />
			E.ExceptionDays,<br />
			E.ExceptionAmount,<br />
			NULLIF(E.ExceptionGST, 0) [ExceptionGST],<br />
			E.ExceptionTotal<br />
	FROM		tblCommissionRun CR<br />
	INNER JOIN 	tblException E ON E.CommissionRunID = CR.CommissionRunID<br />
	INNER JOIN	FastLoan_Applications.dbo.tblIntroducer I ON I.IntroducerID = E.IntroducerID<br />
	INNER JOIN	FastLoan_Applications.dbo.tblLoan L ON L.LoanID = E.LoanID<br />
	INNER JOIN	FastLoan_Applications.dbo.tblApplication A ON A.ApplicationID = L.ApplicationID<br />
	LEFT JOIN	FastLoan_Applications.dbo.trelCustomerApplication CA ON CA.ApplicationID = L.ApplicationID<br />
	LEFT JOIN	FastLoan_Applications.dbo.tblCustomer C ON C.CustomerID = CA.CustomerID<br />
	INNER JOIN	FastLoan_Applications.dbo.tblLoanIncrease LI ON LI.LoanID = E.LoanID<br />
	LEFT JOIN	FastLoan_Applications.dbo.tblLoanIncreaseStage LIS12 ON LIS12.LoanIncreaseID = LI.LoanIncreaseID<br />
		AND	LIS12.LoanStageTypeID = 6<br />
	LEFT JOIN	trefCommissionRunType CRT ON CRT.CommissionRunTypeID = CR.CommissionRunTypeID<br />
	LEFT JOIN	trefExceptionType ET ON ET.ExceptionTypeID = E.ExceptionTypeID<br />
	WHERE	CR.Pending = 1<br />
		AND	CR.CommissionRunTypeID = 3<br />
		AND	E.LoanIncreaseID IS NOT NULL<br />
		AND	CA.ApplicantTypeID = 1<br />
<br />
	ORDER BY	TradingName, SettlementDate<br />
			--CRT.CommissionRunType, CR.CommissionRunDate, SettlementDate<br />
END

Thanks in Advance

Re: Implicit conversion varchar to money

M.H.1.2.32-Nov-07 22:19

M.H.1.2.3

2-Nov-07 22:19

it seems like to me that you would do something like this instead

Case When Isnull(LP.RefinanceAmount, '')='' Then cast(L.LoanAmount as varchar(20))
Else 'N.F' + cast(LP.RefinanceAmount as varchar(20)) END [Amount],

Re: Implicit conversion varchar to money

Giri K4-Nov-07 18:16

Giri K

4-Nov-07 18:16

still got the same error, but anyway thanks for the reply

restor backup in sql

8213001231-Oct-07 9:40

82130012

31-Oct-07 9:40

Hi friends Rose | [Rose]

I want to write a code for restor my database backup. I want to write it in SQL procedure. Whaat I must do? Confused | :confused:

thanks

Re: restor backup in sql

Hesham Amin31-Oct-07 11:23

Hesham Amin

31-Oct-07 11:23

Check SQL Server 2005 Books Online[^]

Hesham A. Amin
My blog

Re: restor backup in sql

Paul Conrad31-Oct-07 14:08

Paul Conrad

31-Oct-07 14:08

If I recall, there should be a stored proc already there to to backups and restores. I could be wrong, though.

"Any sort of work in VB6 is bound to provide several WTF moments." - Christian Graus

SQL Express server con error

solarthur0131-Oct-07 7:36

solarthur01

31-Oct-07 7:36

I am trying to connect to sql express server but i keep getting the following error "Login Failed for user 'ESELD\pit'. The user is not associated with a trusted sql server connection. Can anyone help?

Re: SQL Express server con error

pmarfleet31-Oct-07 8:13

pmarfleet

31-Oct-07 8:13

Have a look at this article[^].

Paul Marfleet

Re: SQL Express server con error

pmarfleet31-Oct-07 13:15

pmarfleet

31-Oct-07 13:15

Isn't the problem that your Windows account doesn't have permissions to access the SQL Server?

The article explains how to grant these permissions.

Paul Marfleet

"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush

Re: SQL Express server con error

Paul Conrad31-Oct-07 14:09

Paul Conrad

31-Oct-07 14:09

Make it a trusted sql server connection. Google can tell you how.

"Any sort of work in VB6 is bound to provide several WTF moments." - Christian Graus

WHERE Clause's Column from a Parameter [modified]

Brian C Hart31-Oct-07 7:13

Brian C Hart

31-Oct-07 7:13

Hello folks,

I am a Newbie. I know I could just do this dynamically, however I am looking for any possible way to keep as much stuff declared at design-time as possible (just a personal preference).

I want to do as is illustrated below -- using the 'Employees' table from the 'Northwind' database. My environment is VS.NET 2005 with SQL server 2005, language is C# and page is ASP.NET.

Basically, I have a DropDown with "City" and "LastName" as the choices, next to a TextBox. The user picks either of the field names in the DropDownList and then types a search filter in the TextBox. I want to use a Parameter to specify the column name and a Parameter to specify the value to search with a WHERE clause. The value is replaced with its appropriate SelectParameter but not the field to search, i.e. the code below is not working:

Default.aspx:

<asp:DropDownList ID="DropDownList1" runat="server">
<asp:ListItem>City</asp:ListItem>
<asp:ListItem>LastName</asp:ListItem>
</asp:DropDownList>
<asp:TextBox ID="TextBox1" runat="server" Width="220px"></asp:TextBox><br />
<br />
<asp:GridView ID="GridView1" runat="server" AllowSorting="True" AutoGenerateColumns="False"
DataKeyNames="EmployeeID" DataSourceID="SqlDataSource1">
<Columns>
<asp:BoundField DataField="EmployeeID" HeaderText="EmployeeID" InsertVisible="False"
ReadOnly="True" SortExpression="EmployeeID" />
<asp:BoundField DataField="LastName" HeaderText="LastName" SortExpression="LastName" />
<asp:BoundField DataField="FirstName" HeaderText="FirstName" SortExpression="FirstName" />
<asp:BoundField DataField="City" HeaderText="City" SortExpression="City" />
</Columns>
</asp:GridView>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:NorthwindConnectionString %>"
SelectCommand="SELECT [EmployeeID], [LastName], [FirstName], [City] FROM [Employees] WHERE ([@Field] LIKE @Value)">
<SelectParameters>
<asp:ControlParameter ControlID="DropDownList1" Name="Field" PropertyName="SelectedValue" Type="String"/>
<asp:ControlParameter ControlID="TextBox1" Name="Value" PropertyName="Text" Type="String" />
</SelectParameters>
</asp:SqlDataSource>

Is there something I am doing wrong? Am I not allowed to say, i.e. SelectCommand="SELECT [field1] [field2] FROM [mytable] WHERE [@param] = @value" where @param upon replacement, specifies the field's name? It is not getting replaced, so naturally SQL Server complains with the error:

Invalid column name '@Field.'

What's up? Why cannot I use a parameter when specifying a field in a WHERE clause?

-- modified at 13:21 Wednesday 31st October, 2007

Re: WHERE Clause's Column from a Parameter

pmarfleet31-Oct-07 7:32

pmarfleet

31-Oct-07 7:32

This article[^] should give you the information you need.

Paul Marfleet

Count errors and not display any less than a value

solutionsville31-Oct-07 7:00

solutionsville

31-Oct-07 7:00

I have this query that works as it is. I want to modify it so that I do not see Alarm errors less than 50.

I guess it would have to be a sub query to this;

SELECT WaysideName, ST, AlarmMessage, COUNT(AlarmMessage) AS Error_Count, CONVERT(char(10), EventDate, 111) as ErrorDate
FROM AlarmsList
where EventDate >@StartDate and EventDate <dateadd(day, 1,@enddate)="" and="" alarmmessage="" not="" like="" ('%off')="" ('%restored')="" <=""> 'OPERATIONAL AND HEALTHY'
GROUP BY WaysideName, ST, AlarmMessage, CONVERT(char(10), EventDate, 111)

Thanks,

Re: Count errors and not display any less than a value

pmarfleet31-Oct-07 7:09

pmarfleet

31-Oct-07 7:09

Try this:

SELECT WaysideName, ST, AlarmMessage, COUNT(AlarmMessage) AS Error_Count, CONVERT(char(10), EventDate, 111) as ErrorDate
FROM AlarmsList
where EventDate >@StartDate and EventDate 'OPERATIONAL AND HEALTHY'
GROUP BY WaysideName, ST, AlarmMessage, CONVERT(char(10), EventDate, 111)
HAVING COUNT(AlarmMessage) >= 50

Adding the HAVING clause modifies the query so that only rows with an alarm message count >=50 are included in the output.

Paul Marfleet

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.