I have stored procedures which have an output parameter with a rowcount.
I used to just access the stored procedure directly. However, I'm separating my data access into a method in my data access class, and the method returns a datatable.
I'm not sure how I'm supposed to access my output parameter anymore.
SQL Server 2000 SP3.Is it possible for the @@ROWCOUNT function to return NULL after astatement? I am troubleshooting a relatively large stored procedure withmultiple SELECT statements and a couple of INSERTs into table variables.Immediately after each statement I save the value returned by @@ROWCOUNT toa local variable. That information eventually is passed back to the clientvia one output parameter, for all statements in the procedure.Occasionally, the value returned via that parameter is NULL. This cannot bereproduced by re-running the SP with the same input parameters.Before doing any further troubleshooting, I would like to rule out thepossibility that @@ROWCOUNT can actually return a NULL under somecircumstances. From searching the archives, it appears that in SQL Server7.0 this could happen in the context of a DML query on a table withtriggers. This is not the case here - the only DML queries are INSERTs intotable variables, all other queries in the SP are SELECTs.Any related information would be appreciated.--remove a 9 to reply by email
View 3 Replies View RelatedHiI'm using an sqldatasource control in my aspx page, and then executing it from my code behind page (SqlDataSource1.Insert()), how do i retrieve the number of rows (@@rowcount) which have been inserted into the database and display it in my aspx page. I am using a stored procedure. thanks
View 1 Replies View Related
I've implemented a solution with application, database and report server on seperate machines. The application is a web app and is Internet facing. What is the best method for executing reports on the RS server that are initiated from the web server? Using URL access requires a login or anonymous access neither of which are desired. Web services works but I loose access to the toolbar. Is there some other way to pull this off where I can let the public access reports and give them access to the toolbar?
Thanks.
I have a vb.net application that executes a simple flat file to sql table dtsx package. I want to capture the rowcount to display back to the user to verify the number of rows that were inserted or updated to the table. I have a Row Count component placed between the flat file source(without errors) and the destination component. I have assigned a variable named RecordCount to the Row Count component. So far so good I hope : )
Now, I also use a variable to "feed" the package the flat file source. This works fine, but I cannot figure out how to retrieve the row count information and how to assign that to the variable RecordCount.
Also, if anyone has any insight on the way to work with the OnProgress method in SSIS I would appreciate that as well. In SQL 2000 using DTS I create a "PackageEventsSink" that I had found online, and it worked great for monitoring the progress of the DTS. Can't seem to figure out how to get it to work in SSIS.
Thanx,
Mike
In sql I perform the following
SELECT * FROM
xlsdci x LEFT OUTER JOIN fffenics f ON f.[derived deal code] = x.[manual dcd id]
which gives me a row count of 2709 rows
In SSIS I have a merge join component (left outer)
left input = xlsdci with a sort order of 1 ASC on [manual dcd id] (OLE DB source component)
right input = fffenics with a sort order of 1 ASC on [derived deal code] (OLE DB source component)
which when run in the IDE gives me a rowcount of only 2594 rows
Why is this so?
Also if I change the join to INNER in the merge join, the number of rows drops dramatically to only 802.
Fair enough, I hear you cry, maybe there are IDs in the 'xlsdci' table that are not in the 'fffenics' table. Ok. But the following SQL reveals that there are only 14 rows(IDs) in 'xlsdci' that are not in 'fffenics'
SELECT * FROM xlsdci
WHERE [manual dcd id] NOT IN (SELECT [derived deal code] FROM dbo.fffenics)
What is going on here?
What I want to do is remove part of a result from a query. I am selecting the srvname from sysservers as follows.
SELECT srvname from sysservers
Where srvid ='0'
When the name is returned some times it might be like servernameinstance. What I need to do is cut off the and everything after that so that my result is just servername. I am then using this result in a cursor to go after server shares. The length of the servername varies. Any suggestions.
Hello:
I'm scheduling DTS packages to run as a job but when I do that, the job siply does not works, someone tolds me that it's a problem with the SQL and Windows NT authentication and security and he gaves me this URL http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q269074.
but it not so clear to me, i'm working with SQL Server 200 and Windows Server 2000.
What can I do?
I dont know how to write a SQL Programming code from the table to the table (As the photo)
Would it be possable to help me?
Thanks so much!
Hi,
Currently I am using the BCP functionality to import/export tables between different SQL Server 2005 databases. The process is working ok, but it creates alot of manual work, when 70 or more tables need to be transferred, every week. I am new to RMO development and am interested if the following may be possible.
- I would like to build an interface in VB.net allowing users to select a source database, and select the tables that need to be transferred from the Source DB, to the Destination DB.
-Put in place RMO funtionality to replicate the source database tables into flat files.
-Within the Vb.net app transfer the created flat files into the destination database.
I would greatly appreciate any advice on this, or if there may be a better approach at replacing the BCP functionality.
Thanks,
Hi,
Need help from experts on web service (ReportingService class).
I am trying to change a datasourcereference of a report from one location to another location. I am kind of stuck at this point where i am able to get the location but only dataSource.Item, dataSource.Name are not enough items for me to solve this problem.
any possible help or any threads already answered these type, greatly appreciated.
thanks
kodru.
Hi!I want some tutorials on how to actually program database applications. Everything seems so bound with the data controls provided by Visual Studio and coming from PHP I don't like to not have full control over my applications. So where can I learn about creating my own database applications without using repeater, gridview or formview?Thank you in advance!
View 1 Replies View RelatedI need to create a database with one table and its column names.
I got this script from the query analyzer templates as a database basic template:
IF EXISTS (SELECT *
FROM master..sysdatabases
WHERE name = N'<database_name, sysname, test_db>')
DROP DATABASE <database_name, sysname, test_db>
GO
CREATE DATABASE <database_name, sysname, test_db>
GO
I am not sure what it all means, what I need to do it name the database a certain name each time. and then create one table with 6 column names.
can someone give me guidance please...
I have a table containing almost 700000 records. The table contains the following fields:
table_id, member#, car_eff_dt, car_term_dt, mbr_occur.
A member can occur more than one time and his effective date/termination date are sorted sequentially.
An example would look like this:
table_id member# car_eff_dt car_term_dt mbr_occur
1 12345678901 01/01/1995 12/31/1995 null
2 12345678901 01/01/1996 12/31/1996 null
3 12345678901 01/01/1997 12/31/1997 null
4 10987654321 07/15/1998 02/16/1999 null
5 10987654321 04/01/2000 12/31/9999 null
Using Transact-SQL, is it possible to generate a unique number for each occurance of a particular member#?
table_id member# car_eff_dt car_term_dt mbr_occur
1 12345678901 01/01/1995 12/31/1995 1
2 12345678901 01/01/1996 12/31/1996 2
3 12345678901 01/01/1997 12/31/1997 3
4 10987654321 07/15/1998 02/16/1999 1
5 10987654321 04/01/2000 12/31/9999 2
Currently, I am running the code in an Access/VBA module through an ODBC connection, but I would like to be able to run all my code directly on the server.
Thanks in advance for any help you might be able to provide.
Just curious,
What do all of you use to create and edit stored procedures? I am looking for a better way to code than to use the default window supplied with Enterprise Manager.
Do most of you use the Query Analyzer? Are there third party products you like using better?
Thanks for the info..
Eugene
I am looking into a production geared software called MassGroup. (www.massgroup.com) It says that its FactoryServer builds are on a standardized SQL framework and web architecture for data collection, work order requests, reporting and asset management. My questions are:
1. Is SQL its own programming language? If not what is it?
Answer: After going thru several pages and reading the fine print I discovered they use VB, ASP, COM technology, and Crystal Reports.
2. How powerful do you think this would be at gathering data and does it have the capability of creating a number sequence automatically? (example: 0001; 0002; 0003...)
Any help would be appreciated.
Thank you in advance!!!!!
hi to all,
What is the best language or compiler used in web database programming. Any have an idea pls help me.
Thankss.
I have just started in Sql 6 months ago. I was an IT major in graduate school and am now in my late 30's (career change). It took me 4 years to finally get what I thought was my dream job. Well, I was promised training which I have yet to receive and bascially went from doing basic queries that I did in class in college to doing advance Sql - stored procs, etc. I completely hate it. I do not know if it is from the lack of training or if it just not for me. I am completely bored out of mind. I am also tired of struggling everyday to complete assignments. The first 2 assignments I received were way out of my league. I was told this by a project mgr who did a similiar assignment and they have years of sql experience and it took her 3 months with assistance to complete it. I have been with the company for almost 6 years. I am not sure if I should just complete my current assignments and ask if they can reassign me to a different IT department, look for a new job or just continue to struggle and pull my hair out. Please give me some feedback as to how you would handle this situation.
View 8 Replies View RelatedGuys ..Im a very very confused Jr DBA/Dev here !
I need to know what exactly other languages [C#/VB.net/ADO/XML/ASP.net..etc] that I should learn in order for me to be able to excel in SQL Development.
I started getting my hands dirty with some SSIS / SSRS & SSAS, but inorder for me to handle situations such as [Find if file exist in ftp before loading, Checking a certain row of a data file before processing/ Zipping and Unzipping data files and etc...] What should I need to know well in order for me to add more value to my skills?
I would like to learn 1 language and master it pretty well.What are your suggestions?
And what is this CLR that is being talked about ?Does it use other language besides SQL?
Do guide this lost JR .......
Are there any good resources (books, websites, etc) for programming SQLServer using Visual C++? I am currently using C# but I really want to learnhow to do it using C++, but it appears to be much more difficult using C++.Thanks
View 2 Replies View Relatedi need to access an .sdf database using microsfot visual forpro
what drivers/connection strings do i need?
I currently have a table with the fields:
- StudentID as an Identity and is the Primary Key field (PK)
- FName as varchar(15)
- LName as varchar(20)
When I use a combo box, I place the information in it as follows:
- LName, FName/StudentID Ex: Rogers, James/4
In order to find our the student I want, I parse the above field for the StudentID.
Question:
How can I display the student's name only and still get to their StudentID?
Would I have to use an array holding/pointing to the StudentID? Or What?
Thanks In Advance.
Hi all,
I know how to basically change SSIS variables from within a C#. NET winform application. But can anyone point me on how to change the contents of a connection manager SSIS connection from withing C#.net please. That is..how do i parameterize a connection manager connection to basically accept the connection information from within the winform app.
Thanks
Hey, i have an installer that places ms sql express onto a machine. thats is fine. i want to create a post setup application that runs after ms sql server is installed. what for example i want to do is be able to create a user and have the DB allow remote connections. i can provide instructions for a 'peon' to make the required post setup changes but i want to see if i can cover that with an application. thanks
Charles Rezsonya
Hi..
I am working on sql server integration services..New to this topic...I want to transfer data from flat file to sql database by doing it programatically.Creating source,destination and transform adapters
my basic question is should the source,transform and destination adapter be bulid for each file i am trying to transfer.
What is the advantage of this doing it this way.
Would anyone let me know about this..
Thanks
Not sure if this is right place to ask this. I have a table where I want a single field to show up in separate columns. (wow its hard to describe what I want. Here is an example
Store_Group_Product_Tbl
store, group id, product
Let's say we have these records:
Store 1 - group 1 - hats
Store 1 - group 1 - clothes
Store 1 - group 1 - shoes
Store 1 - group 2 - drink
Store 1 - group 2 - food
I want my select statement to return:
Store 1 - group 1 - hats - clothes - shoes
Store 1 - group 2 - drink - food - NULL
(and in this case I will be selecting on where store = a selected store)
Is there a way to join it back to itself to get the data I want? Or will I have to select everything into an array and then manipulate that array to get everything columned up correctly?
Could some one please help in coding a OLE DB Command, to fire a stored procedure which takes 2 parameter's
Help me in the coding, by which I should be able to fire a SP that takes 2 parameters, by mapping 2 feilds from the source table to the SP parameter's
The Below sample fires a SP without any parameter's
ConnectionManager cm = package.Connections.Add("OLEDB"); cm.Name = "OLEDBConnection"; cm.ConnectionString = "Data Source=.;Initial Catalog=AdventureWorks;Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Auto Translate=False;"; // Create the source component.
IDTSComponentMetaData90 component = dataFlow.ComponentMetaDataCollection.New(); component.Name = "OLEDBSource";
component.ComponentClassID = "DTSAdapter.OleDbSource"; // Get the design time instance of the component.
CManagedComponentWrapper instance = component.Instantiate(); // Initialize the component
instance.ProvideComponentProperties(); // Specify the connection manager for Source.
if (component.RuntimeConnectionCollection.Count > 0) {
component.RuntimeConnectionCollection[0].ConnectionManagerID= package.Connections["OLEDBConnection"].ID;
component.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.ToConnectionManager90(package.Connections["OLEDBConnection"]); } // Set the custom properties. instance.SetComponentProperty("AccessMode", 2); instance.SetComponentProperty("SqlCommand", "Select * from HumanResources.Employee");
// Reinitialize the metadata. instance.AcquireConnections(null); instance.ReinitializeMetaData(); instance.ReleaseConnections();
IDTSComponentMetaData90 m_SQLCommand = null;
m_SQLCommand = dataFlow.ComponentMetaDataCollection.New(); m_SQLCommand.ComponentClassID = "DTSTransform.OLEDBCommand.1"; IDTSDesigntimeComponent90 oledbDesignTime = m_SQLCommand.Instantiate(); oledbDesignTime.ProvideComponentProperties();
if (m_SQLCommand.RuntimeConnectionCollection.Count > 0){m_SQLCommand.RuntimeConnectionCollection[0].ConnectionManagerID = package.Connections["OLEDBConnection"].ID; m_SQLCommand.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.ToConnectionManager90(package.Connections["OLEDBConnection"]);} oledbDesignTime.SetComponentProperty("SqlCommand", "Exec Test");
Hello,
I am trying to bring the data from Oracle to SQL Server. And, I need to write this in C#. All that I have is a source table in Oracle and a source table in SQL Server. I need to have some samples.
Can you please guide in the right direction?
Your response will be helpful.
thx,
amrith
Hi ,
i am using VS.NET 2003 for programming and SQL sever 2005 is my database.
while creating DTS packages,
i am using code as follows
Public Sub Task_Sub4(ByVal goPackage As Object)
Dim oTask As DTS.Task
Dim oLookup As DTS.Lookup
Dim oCustomTask4 As DTS.DataPumpTask2
oTask = CType(goPackage, DTS.Package).Tasks.New("DTSExecuteSQLTask")
oCustomTask4 = CType(oTask.CustomTask, DTS.DataPumpTask2)
oCustomTask4.Name = "Copy Data from contact_info to [content_shriya].[dbo].contact_info] Task"
oCustomTask4.Description = "Copy Data from contact_info to [content_shriya].[dbo].[contact_info] Task"
oCustomTask4.SourceConnectionID = 3
oCustomTask4.SourceSQLStatement = "select * from [Content_management].[dbo].[contact_info]"
oCustomTask4.DestinationConnectionID = 4
oCustomTask4.DestinationObjectName = "[content_shriya].[dbo].[contact_info]"
oCustomTask4.ProgressRowCount = 1000
oCustomTask4.MaximumErrorCount = 0
oCustomTask4.FetchBufferSize = 1
oCustomTask4.UseFastLoad = True
oCustomTask4.InsertCommitSize = 0
oCustomTask4.ExceptionFileColumnDelimiter = "|"
oCustomTask4.ExceptionFileRowDelimiter = vbCrLf
oCustomTask4.AllowIdentityInserts = False
oCustomTask4.FirstRow = 0
oCustomTask4.LastRow = 0
oCustomTask4.FastLoadOptions = 2
oCustomTask4.ExceptionFileOptions = 1
oCustomTask4.DataPumpOptions = 0
'Call oCustomTask4_Trans_Sub1(oCustomTask4)
goPackage.Tasks.Add(oTask)
oCustomTask4 = Nothing
oTask = Nothing
End Sub
But i am geeting error in line
oCustomTask4 = CType(oTask.CustomTask, DTS.DataPumpTask2)
giving error 'System.InvalidCastException'
Additional information: Specified cast is not valid.
i changed it with oCustomTask4 = oTask.CustomTask ,
then also similar error appear.
Please solve my problem, i need to create DTS Packages by using VS.NET 2003 and Database is SQL server 2005.
Thank you
I am working on Sql Server 7.0 with a group of Visual Basic programmers that have made the following request:
1.They want all decimals/integer fields in the database to default to 0(zero).
Is there an way to do this for all fields instead of on an individual basis?
2.They don't want a null returned on any character string field.
What is the best way to take care of these 2 requests? Thanks in advance.
Karen Suenram
Hi-
Can you give me any help/assistance on the development process involved with migration from MS Access to SQL Server 7.
Specifically, I need my current MS Access data migrated to SQL, and then I need two (2) fairly simple forms developed for data input and searching on the web.
The developers I have been speaking too seem to want to buy their Hawaiian condo from this one project...which seems like A LOT OF $$ for the development of an off-the-shelf product that costs $15,000.
Any leads and/or thoughts would be GREATLY APPRECIATED.
THANKS.
Hi all,
Anyone has sample code on accessing MS SQL or Access using C programming? I just need simple INSERT to the database. Thank you.
Regards,
Kian Ann
Hi,
Is there a way of looping thru a result set inside a stored proc?
Any good resources/sites for programming stored procedures would be very much appreciated.
Thanks
:)
Steve