Just i modified one sub stored procedures which is not getting affected in the main stored procedures
Create procedure main
as
begin
set nocount on
--First sp
exec Data_transfer_sp
--Second sp
Exec Clearance_sp
set nocount off
end
In data_transfer_sp,i have commented the select statement
but still iam use to the select result while execution the main sp.
Note:I have compiled the data_transfer_sp after making comment
.Txs in advance
I am using vs 2010 to write my dtsx import scripts.I use a script component as a source to create a flat file destination file.Everything have been working fine,but then my development machine crashed and we have to install everything again.Now when i use the execute package utility to test my scripts i get the following error:
Error system.NullReferenceException: Object refrence not set to an instance reference.
In PreExecute section TextReader = new system.io.streamreader(" file name") In the CreateNewOutputRows: dim nextLine as string nextLine = textReader.ReadLine
[code]...
is there something which i did not install or what can be the error?
DECLARE Loan_cursor CURSOR FOR SELECT Loan_No,store FROM loan WHERE maturity_date > '2001-04-30' and loan_no like 'ABL%'
OPEN Loan_cursor
-- Perform the first fetch. FETCH NEXT FROM Loan_cursor
-- Check @@FETCH_STATUS to see if there are any more rows to fetch. WHILE @@FETCH_STATUS = 0 BEGIN Declare @LoanNo varchar(12) ** Set @LoanNo = Loan_No
-- This is executed as long as the previous fetch succeeds. FETCH NEXT FROM Loan_cursor END
CLOSE Loan_cursor DEALLOCATE Loan_cursor
when l run the cursor l get the error Server: Msg 207, Level 16, State 3, Line 15 Invalid column name 'Loan_No'. . If l reference it as Set @LoanNo = LoanTable.Loan_No l get the error
Server: Msg 107, Level 16, State 3, Line 15 The column prefix 'loan' does not match with a table name or alias name used in the query. All l'm trying to do is to compare the loan number that l get from the cursor to the value in the loans table. Maybe reference is a better word
Can anyone see why I would get the 'Object Referenece not set to an instance of an object error in the following code? It happens on this line: MyAdapter.SelectCommand = New SqlCommand(SelectStatement, MyConnection) 'Populate the text boxes from database for alumni fields. Dim MyAdapter As SqlDataAdapter Dim MyCommandBuilder As SqlCommandBuilder Dim DetailsDS As DataSet Dim Row As DataRow Dim SelectStatement As String = "Select ClientID,ClassYear,HouseName,CampusName,EducationMajor,GraduationDate FROM tblclient Where [ClientID]=" & _ ClientIDSent Dim ConnectStr As String = _ ConfigurationManager.ConnectionStrings("TestConnectionString").ConnectionString Dim MyConnection As SqlConnection = New SqlConnection(ConnectStr) MyAdapter.SelectCommand = New SqlCommand(SelectStatement, MyConnection) MyCommandBuilder = New SqlCommandBuilder(MyAdapter) MyAdapter.Fill(DetailsDS, "tblClient")
I am access SQL 7 via ADO in some ASP pages. My database objects were created under a user name aliased to the dbo. As a result, when I have my client logon to make a connection, I have to preface all my object references with the name of the owner, i.e., Select * from mydboname.people . Is there a way to avoid having to prefix all my transact sql statements with the owner?
I have two tables that are reference to each other by foreign key, now I would like to alter the table so the it doesn't reference each other any more. What is the syntax to alter a table so that the fk field doesn't reference a table anymore. Thanks in advance for the help.
As I posted earlier, I am relatively new to TSQL. I have been given a few books: O'Reilly SQL on SQL Server 2005 O'Reilly SQL in a Nutshell Mastering SQL Server 2005 - Sybex Beginning SQL Server 2005 Programming - Wrox
I have a copy of SQL2005 Developers Edition on a development server. So I think I have some decent tools at my disposal.
I was wondering if there are any other sites or forums that might be suggested for learning SQL programming? Right now, I am just making stuff up and following examples in books, etc. I would like to find a site where there are beginner level projects for no other purpose than educational. Something where I can get more hands on of the design and programming aspects. Just to practice.
CREATE TABLE s (sno VARCHAR(5) NOT NULL PRIMARY KEY, name VARCHAR(16), city VARCHAR(16) )
CREATE TABLE p (pno VARCHAR(5) NOT NULL PRIMARY KEY, descr VARCHAR(16), color VARCHAR(8) )
CREATE TABLE sp (sno VARCHAR(5) NOT NULL REFERENCES s, pno VARCHAR(5) NOT NULL REFERENCES p, qty INT CHECK (qty>=0), PRIMARY KEY (sno, pno) )
when i run this query i get an error saying:
Column 's.sno' is not the same length as referencing column 'sp.sno' in foreign key 'FK__sp__sno__5EBF139D'. Columns participating in a foreign key relationship must be defined with the same length.
Now i have a reauirement to build a stored proc in which all the transactions starting from one transaction like, if i want to know the chain for item no 2 it shall give the following result:
24 43 37
if i want to know the chain for item no 3 then it shall give following 37
if i want to know the chain for item no 1 then it shall give following 12 24 43 37
I need to parse SQL statements directly and extract each segmentindividually. Is there a way reference the Microsoft SQL Parserdirectly from VB.Net?Thanks!*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
Hello. I'm getting an error - "Object reference not set to an instance of an object" when running an SSRS report with graphs. It only happens when the returned dataset is empty (based on parameters set by the user, it is possible to return no rows). I've tried using the NoRows property (I entered "No Data" in the property box), but I still get the same error. Is there another way to account for an empty dataset in charts?
I would like to use a custom build class library written in C# inside of the vb script. Does anyone know how to add the reference to the scriptComponent project once you open script through design script button?
Given a UDT, is there any way to get a reference to the table where the specific instance is running ?
IE: Let's suppose we have defined a UDT named UDTPoint; now we define two tables: ATable & BTable, wich both have one column that is defined as UDTPoint.
When an insert/update/delete operation on ATable or BTable occurs, the UDTPoint class needs to verify in which context it is running (ATable or BTable) before doing operations on data.
Hi, I wanted to add a reference to an assembly in my report (not a DLL, but an EXE). As I see there is no option to select an assembly which is an EXE. Firstly, is it possible to add a reference to an EXE (I guess not)? Secondly, If no, why is it not possible?
The table above is my users table. It allows for a user to be at multiple sites or multiple locations within a single site or multiple sites. Would it be wise to use a auto incrementing primary key instead of the 3 column composite key? The reason I ask is because if I am referencing this SU table (which I will be a lot), a lot more data would be replicated to the tables which have the foreign key to this table, right? But if I used a single incrementing column as the primary key, only a small integer would be used as the foreign key, saving space?Does this make sense?
I have a .NET assembly that I need to reference and use within my SSIS script task. The only way I can acheive this is to strongly name my assembly and put it in the GAC. This is not possible as my assembly is already in production and uses other 3rd party assemblies that would also need to be registered in the GAC. As a workaround, I have created a .Net console application that references my assembly, that call from a SSIS Process Task. Does anyone know of another way I could use my .NET assembly within my SSIS package? Any help appreciated. Regards, Paul.
Please help as I've no idea what to try next! I had a 2003 project designed by someone else. I upgraded it to 2005 using the conversion wizard. Now it all works find within vs2005, I deploy it to a location else where on my pc and create a virtaul directory and map it so that I'm using IIS. This all works fine. As soon as I move it to a virtual machine (windows 2k server SP4 IIS5) I get object reference not set to an instance, basically what I can determine is my sqlconnection fails when it is opened. Can anyone tell me? Private Sub DoLogin(ByVal bLogin As Boolean) If bLogin Then strQueryString = "exec MyQuery'" End If dsVerifyPW = GetDataSet(strQueryString, Application("SQL_Connect")) '*** ....... web.config <configuration> <appSettings/> <connectionStrings> <add name="MyConnectString" connectionString="Server=(local);Database=MyDB;Persist Security Info=True;User ID=Uzer;Password=Uzerpwd" providerName="System.Data.SqlClient"/> </connectionStrings> <system.web> *****************************Below from App_Code folder MyModule.vb Imports System.Data Imports System.Data.SqlClient Imports System.Security.Cryptography Imports System.Web.Mail Imports System.Text.RegularExpressions
Namespace My.NET
Public Module MyModule Public Function GetDataSet(ByVal strSQL As String, ByVal strSQL_Connect As String) As DataSet Try Dim cnSQl As New SqlConnection(strSQL_Connect) cnSQl.Open() '**********fails here Dim dsLoad As New DataSet Dim cmdLoad As New SqlCommand(strSQL, cnSQl) Dim daLoad As New SqlDataAdapter(cmdLoad) daLoad.Fill(dsLoad, "ReturnedData") GetDataSet = dsLoad cnSQl.Close() Catch sqlex As SqlException GetDataSet = Nothing Catch ex As Exception GetDataSet = Nothing End Try End Function
from Global.asax.vb on application start up Application("SQL_Connect") = ConfigurationManager.ConnectionStrings("VacationConnectString").ConnectionString
I have one table name Magazine_Details, it has a composite primary keyCreate table Magazine_Details ( MagazineTitle varchar(60), MagazineType varchar(20), IssueType varchar(25), IssueNumber varchar(10), Qnty int, Qnty_in_Hand int, [Date] datetime constraint PK_Magazine_Details_Composite primary key(MagazineTitle,IssueNumber) ) Another table is Magazine_Issued and i want reference from Magazine_Details table. but i am auable to create foreign key constaraint in Magazine_Issued table.create table Magazine_Issued ( Member_ID varchar(100), MagazineTitle varchar(60) references Magazine_Details(MagazineTitle), IssueNumber varchar(10) references Magazine_Details(IssueNumber), IssueDate datetime, Magazine_Status varchar(10) )
Hi All, Can anyone tell me how to create a reference for composite key. For ex, I have created tblEmp table successfully. create tblEmp( empId varchar(100), RegId varchar(100), empname varchar(100),constraint pk_addprimary key(empId, RegId) ) And now, I am going to create another table which references the composite key.create table tblAccount( acctId varchar(100) primary key, empId varchar(100) references tblEmp(empId), RegId varchar(100) references tblEmp(RegId) ) But it gives error like Server: Msg 1776, Level 16, State 1, Line 1There are no primary or candidate keys in the referenced table 'tblEmp' that match the referencing column list in the foreign key 'FK__tbl'.Server: Msg 1750, Level 16, State 1, Line 1Could not create constraint. See previous errors. Could anyone please let me know how to create reference for composite key. Thanks in advance,Arun.
Hi,I have an update command to update a column by '1' on click. I am getting the error: Exception Details: System.NullReferenceException: Object reference not set to an instance of an object.It doesnt give which code it reffers to but the stack trace is:[NullReferenceException: Object reference not set to an instance of an object.] Buyers_MyPurchases.Button1_Command(Object sender, CommandEventArgs e) +63 System.Web.UI.WebControls.Button.OnCommand(CommandEventArgs e) +75 System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +155 System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7 System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11 System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +4886 My code behind is: private bool ExecuteClickin(int quantity) { SqlConnection con = new SqlConnection(); con.ConnectionString = "Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\ASPNETDB.MDF;Integrated Security=True;User Instance=True"; con.Open(); SqlCommand command = new SqlCommand(); command.Connection = con; TextBox TextBox1 = (TextBox)DataList1.FindControl("TextBox1") as TextBox; command.CommandText = "UPDATE Items SET numberclickedin = numberclickedin + 1 WHERE productID=@productID"; command.Parameters.AddWithValue("@productID", TextBox1.Text); command.ExecuteNonQuery(); con.Close(); command.Dispose(); return true; } protected void Button1_Command(object sender, CommandEventArgs e) { if (e.CommandName == "Clickin") { TextBox TextBox1 = DataList1.FindControl("TextBox1") as TextBox; bool retVal = ExecuteClickin(Int32.Parse(TextBox1.Text)); if (retVal) Response.Redirect("~/Buyers/inbox.aspx"); } }} Thanks if anyone can work this out. I am sure that no label is null.. Jon
Hi,I have an update command to update a column by '1' on click. I am getting the error: Exception Details: System.NullReferenceException: Object reference not set to an instance of an object..I debugged it and it gave me a warning, which I rectified, but the exception still stands. It happens when the user clicks the button to make the update.. Here is the stack trace:[NullReferenceException: Object reference not set to an instance of an object.] Buyers_MyPurchases.ExecuteClickin(Int32 quantity) +165 Buyers_MyPurchases.Button1_Command(Object sender, CommandEventArgs e) +127 System.Web.UI.WebControls.Button.OnCommand(CommandEventArgs e) +75 System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +155 System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7 System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11 System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +4886 and the code: private bool ExecuteClickin(int quantity) { SqlConnection con = new SqlConnection(); con.ConnectionString = "Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\ASPNETDB.MDF;Integrated Security=True;User Instance=True"; con.Open(); SqlCommand command = new SqlCommand(); command.Connection = con; TextBox TextBox1 = (TextBox)DataList1.FindControl("TextBox1") as TextBox; command.CommandText = "UPDATE Items SET numberclickedin = numberclickedin + 1 WHERE productID=@productID"; command.Parameters.AddWithValue("@productID", TextBox1.Text); command.ExecuteNonQuery(); con.Close(); command.Dispose(); return true; } private bool ExecuteClickonce(int quantity) { SqlConnection con = new SqlConnection(); con.ConnectionString = "Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\ASPNETDB.MDF;Integrated Security=True;User Instance=True"; con.Open(); SqlCommand command = new SqlCommand(); command.Connection = con; TextBox TextBox1 = (TextBox)DataList1.FindControl("TextBox1") as TextBox; command.CommandText = "UPDATE Transactions SET clickedin = clickedin + 1 WHERE productID=@productID AND Usersname = @user"; command.Parameters.AddWithValue("@User", System.Web.HttpContext.Current.User.Identity.Name); command.Parameters.AddWithValue("@productID", TextBox1.Text); command.ExecuteNonQuery(); con.Close(); command.Dispose(); return true; } protected void Button1_Command(object sender, CommandEventArgs e) { Button btn = sender as Button; DataListItem dli = btn.NamingContainer as DataListItem; if (e.CommandName == "Clickin") { TextBox TextBox1 = dli.FindControl("TextBox1") as TextBox; bool retVal = ExecuteClickin(Int32.Parse(TextBox1.Text)); if (retVal) Response.Redirect("~/Buyers/inbox.aspx"); } } I cant work out what the issue is, please could someone give me a hand in identifying it... Thanks so much if you can!Jon
