Retrieve Select Information From A Database
Mar 31, 2008If I had a bunch of paragraphs stored in a database field, is there a way, when displaying that data, that I can write instructions to only retrieve the very first paragraph?
View 4 RepliesIf I had a bunch of paragraphs stored in a database field, is there a way, when displaying that data, that I can write instructions to only retrieve the very first paragraph?
View 4 RepliesHi,
I am working on inserting information into a DB and then retrieving the ID created for that Data to use elsewhere in my code. I have the code below but I do not know how to get toOutput parameter. Can anyone please help?
CREATE PROCEDURE dbo.InsertProduction
@DATEOUT datetime(8),
@DATEREQUIRED datetime(8),
@PREPAREDBY varchar,
@COMMENTID INteger,
@TOTALQUANTITY INteger,
@VENDORID INteger,
@WPO varchar,
@TCAPONUMBER INteger,
@APPROVEDBY varchar,
@Identity int OUT
AS
INSERT INTO PRODUCTION (DATEOUT,DATEREQUIRED, PREPAREDBY, COMMENTID, TOTALQUANTITY, VENDORID, WPO, TCAPONUMBER, APPROVEDBY) VALUES( @DATEOUT, @DATEREQUIRED, @PREPAREDBY, @COMMENTID, @TOTALQUANTITY, @VENDORID, @WPO, @TCAPONUMBER, @APPROVEDBY)
SET @Identity = SCOPE_IDENTITY()
'collect all the information from the form and then apply all and then update
'Get a reference to the Production table.
Dim dtProduction As DataTable = DS.Tables("Production")
Dim dtLineItem As DataTable = DS.Tables("LineItems")
' Create the SqlCommand to execute the stored procedure.
Production.InsertCommand = New SqlCommand("dbo.InsertProduction", connection)
Production.InsertCommand.CommandType = CommandType.StoredProcedure
' Add the parameter for the CategoryName. Specifying the
' ParameterDirection for an input parameter is not required.
'Production.InsertCommand.Parameters.Add("@CategoryName", SqlDbType.NVarChar, 15, "CategoryName")
Production.InsertCommand.Parameters.Add("@DATEOUT", SqlDbType.DateTime, 8, "CategoryName")
Production.InsertCommand.Parameters.Add("@DATEREQUIRED", SqlDbType.DateTime, 8, "CategoryName")
Production.InsertCommand.Parameters.Add("@PREPAREDBY", SqlDbType.VarChar, 50, "CategoryName")
Production.InsertCommand.Parameters.Add("@COMMENTID", SqlDbType.Int, 4, "CategoryName")
Production.InsertCommand.Parameters.Add("@TOTALQUANTITY", SqlDbType.Int, 4, "CategoryName")
Production.InsertCommand.Parameters.Add("@VENDORID", SqlDbType.Int, 4, "CategoryName")
Production.InsertCommand.Parameters.Add("@WPO", SqlDbType.VarChar, 50, "CategoryName")
Production.InsertCommand.Parameters.Add("@TCAPONUMBER", SqlDbType.Int, 4, "CategoryName")
Production.InsertCommand.Parameters.Add("@APPROVEDBY", SqlDbType.VarChar, 50, "CategoryName")
' Add the SqlParameter to retrieve the new identity value.
' Specify the ParameterDirection as Output.
Dim parameter As SqlParameter = Production.InsertCommand.Parameters.Add("@Identity", SqlDbType.Int, 0, "ProductionID")
parameter.Direction = ParameterDirection.Output
' Create a new row with the same schema.
Dim dr As DataRow = dtProduction.NewRow()
'you need the ID from this to insert into the Production DB
' Set the value of all the columns.
dr("DateOut") = CDate(DateTimePicker1.Text)
dr("DateRequired") = CDate(DateTimePicker2.Text)
dr("VendorID") = CInt(vendorbox.SelectedValue)
dr("HomeAddress") = txtApproved.Text.ToString
dr("ApprovedBy") = txtPrepared.Text.ToString
dr("TCAPO") = CInt(txtTCAPO.Text.Trim)
dr("CommentID") = CommentID
dr("TotalCost") = CDec(txtTotals.Text)
dr("TotalQuantity") = CInt(txtQtyTotal.Text)
' Add to the Rows collection or table .
dtProduction.Rows.Add(dr)
'Update the Production Table and then retrieve the ID created in this case
Production.Update(dtProduction)
Dollarjunkie
Dear list,I am trying to get the names of the tables and the column names from thecode below for a database but it is not working. When I run the querybelow the column titles are delivered but there is no data. I think thismight be a premissions issue. Has anyone run into this before?Thanks in advance.Use Test_db/* Provides Table Name, Column Name, Extened Description */Select a.name as tbl_name, b.name as column_name, d.name as data_type,d.length as length, d.xprec as prec, d.scale as scale, b.usertype,b.scale, c.valuefrom sysobjects as a inner join syscolumns as b on a.id=b.id inner joinsysproperties as c on b.colid=c.smallid and a.id=c.idinner join systypes as d on b.xtype=d.xtype
View 2 Replies View RelatedI have a report and it retrieves information from an etl logging table which contains this info: etl_process_name, active_since_date, last_run_date.
When the packages are executing, the table cant be queried because it is in a transaction. Therefore the report keeps generating :s
The thing I want is that: If I query that table (with the report to show the logging table information) and the packages are executing, I want to see the values of the table before the transaction.
What I tried:
From sql management studio:
ALTER DATABASE MyDataBase
SET ALLOW_SNAPSHOT_ISOLATION ON
set transaction isolation level snapshot
go
SELECT etl_process, last_run, active_since FROM config.etl_settings
I did this while the packages were executing and it works but it won't work when I execute the same SELECT statement from the Dataset in the report. How is that possible? Does it have something to do with setting the database options for all users?
Additional info:
I'm database owner
The report solution has a shared datasource which refers to the same database
Hi, all..
I want to know the query to retrieve Column information that we can see from table Design view of Enterprise manager, such as Column name, Pk or not, FK table, Data Type, Null or not, Description(Specially Descrition).
Is there any sp for this or any query for this?
Thank you all...
Hi, I'm getting this error
[Microsoft][ODBC SQL Server Driver][SQL Server]Could not retrieve replication information for table 735393739. Verify that the table has a primary key, and then rerun the Log Reader Agent.Please help me!
Hello,
I need to retrieve all the information about all the foreign key constraints,inorder to store them temporarily (to be deleted later)and then recreated after making the necessary modifications to the concerned tables.
The stored proc sp_helpconstraint shows all the constraint types, their user-defined or system-supplied name, the columns on which they have been defined, and the expression that defines them.But I don't know whether it can be manipulated to get what I want.I need to get it done programmatically..so that I can integrate it in my program which I'm building up progressively.
Any help or scripts would be appreciated!
Hi All,
I am in process of transfering data from Sybase to Sql Server using SSIS 2005
have taken a Data Flow Task in Control Flow tab
In Data flow tab, I have taken one Ole DB Source and One OLe DB Destination
For the source, I am using Sybase Adaptive Server Anywhere Provider 8.0
For Destination, I am using Sql Server 2005 database
In Ole Db Source Editor ,
For OLe Db Connection Manager, I choose Sybase Connection
For Data access mode, I choose Table or View
For Name of the table or the view, I choose a table by name Table1( it lists all the tables from Sybase database)
When i click on preview button or Columns link, I get the following Error
Error at Data Flow Task [OLE DB Source [1]]: An OLE DB error has occurred. Error code: 0x80040E21.
Error at Data Flow Task [OLE DB Source [1]]: Unable to retrieve column information from the data source. Make sure your target table in the database is available.
------------------------------
ADDITIONAL INFORMATION:
Exception from HRESULT: 0xC020204A (Microsoft.SqlServer.DTSPipelineWrap)
Please help me out.
Thanks in Advance
Srinivas
Till recently we were using the following code to retreive schema information of columns of tables
Dim schemaTable = connection.GetOleDbSchemaTable( _
System.Data.OleDb.OleDbSchemaGuid.Columns, _
New Object() {Nothing, Nothing, tableName, Nothing})
Now instead of getting the name of table (which i was using as param for filtering) i'm going to receive a sql-query. Now my question is if I were to get a query like the following :
SELECT
[EmployeeID],
[Title] + ' ' + [LastName] + ' ' + [FirstName] AS FullName,
[BirthDate],
[Address],
[City] + ', ' + [Region] + ', ' + [Country] + ' - ' + [PostalCode] AS FullAddress
FROM [dbo].[Employees]
Then how can I retrieve the schema information of only the columns present in the query.
(Its possible that i might get a query with multiple tables with joints)...
Hi,
I am trying to set up a data flow task. The source is "SQL Command" which is
a stored procedure. The proc has a few temp tables that it outputs the final
resultset from. When I hit preview in the ole db source editor, I see the
right output. When I select the "Columns" tab on the right, the "Available
External Column List" is empty. Why don't the column names appear? What is
the work around to get the column mappings to work b/w source and
destination in this scenario.
In DTS previously, you could "fool" the package by first compiling the
stored procedure with hardcoded column names and dummy values, creating and
saving the package and finally changing the procedure back to the actual
output. As long as the columns remained the same, all would work.
Thats not working for me in SSIS.
Thanks in advance.
Asim.
Hello, I am getting very frustrated! I have got a Foreach loop container which I am processing files within a folder. I have a flatfile connection manager which I have set up using a test file and have updated the expressions attribute to be the package variable I set up in the collection for the loop container however everytime I run it I get the error: 0xC0202094 cannot retrieve the column information from the flatfile connection manager. I can only guess that it is either the variable being passed to the connection manager or the way I set up the connection manager. When I msgbox the variable in a script component before the dataflow step, the variable for the file seems fine. Any suggestions are REALLY appreciated.
Yours
Jayne
I've developed a package that is working well at development machine from VS 2013 for a sample flat file. Also, over development machine, I've deployed it to SSISDB catalogue and even from there also it is running well for the same file.When the same package is deployed to production server's SSISDB catalogue database, it throws following error while processing the same sample flat file, “Unable to retrieve column information from the flat file connection manager”
View 5 Replies View RelatedHey guys,
I was wondering if this is possible I have to be able to just select the number before the '-' in acolumn which is formatted like 123-456 so I just need the the 123 part.
Any ideas how this can be achieved?
Thanks.
hi guys!Currently i'm facing a bit odd situation. i cant modify my code so ihave to make changes in SQL Server.the problem is, i've modified one table by adding new column and now iwant only those row which has not null value in that column wheneverthe select statement gets fire. i mean in every select statement onlythose rows should be returned which has NOT NULL value in the newlyadded column.for this i've created one view and tried to run the application but thei'm facing some problem as my application is developed in .net 2.0,i've used lots of .net functionalities and now i cant make any changesin Code.another solution i think is triggers. if i can fire a trigger beforeany select statements, my problem can be solved instantly but i'mafraid i can not write andy "before select" trigger.if anyone has any idea or any other solution to my problem, please letme know.all suggestions are welcomed.thanks,Lucky
View 4 Replies View RelatedSorry if this is rudimentary.
I need a select statement to retrieve what version of sql server is running and whether it is enterprise or standard.
I know I could just right click but I have hundreds of servers to go through so this should save alot of time.
Thanks,
Kraig
How can i get the product information otherthan Select @@Version
Thanks in advance
WillSmith
mail2willsmith@yahoo.com
I have a sql query where i want to find the highest number in a column. I believe i am querying for the data properly, but i think my problem is that i don't know how to retrieve that information in code. The error message i get is this:"Conversion from type 'DBNull' to type 'Integer' is not valid"Here is my code:------------------------------------------------------------- queryString = "SELECT MAX(SortOrder) as HighestNumber " & _ "FROM uQI_Questions WHERE (SurveyID = @SurveyID)"........ more code in between ....... ' Make sure there is 1 and only 1 record returned. If ds.Tables(0).Rows.Count = 1 Then ' retrieve the number and add 1 to it. NextNumber = ds.Tables(0).Rows(0)("HighestNumber") NextNumber += 1 Else ' Else it must be the FIRST record so return '1'. NextNumber = 1 End If-------------------------------------------------------------The line that generates the error is "NextNumber = ds.Tables(0).Rows(0)("HighestNumber")".Any suggestions?
View 1 Replies View RelatedParameter Information cannot be derived from SQL statements with sub-select queries. Set Parameter information before preparing command.
Here's the query:
update GCDE_SEQ
set LAST_NO = (select max(FLD_NO)
from PONL_FLD)
,UPDT_USER = ?
,UPDT_DT = getdate()
where SEQ_NM = 'FLD_NO'
Why can't Execute SQL Task handle this simple query? I figure i can use 2 SQL Execute SQL Task, one to get the max into a var, and the other to do the updating. However, this is alot of trouble since i'm having this almost exact query in alot of places. Any way around this?
Hello,
I am trying to write a query that will be able to select different segments of data based on spelling of the last name.
For example, in my database of name information, I need to select anyone whose last name starts with 'AAA' to 'EJJ'
then need to select anyone whose last name starts with 'EJK' to 'JAE' and so on...
I have tried using LIKE and some other methods with the > operator, but I can't get it to work. Does anyone have any suggestions or ideas on how to select data based on the alphanumeric characters this way?
Thanks
Is it possible to retrieve Distinct rows from this Select Statement?
I'm getting the correct results, but duplicate rows because some customers place more than one order on the same day.
Code:
SELECT dbo.Customers.CustomerID, dbo.Customers.Title, dbo.Customers.FirstName, dbo.Customers.LastName, dbo.Customers.CustomerEmail, dbo.Customers.DateCreated,
CONVERT(char, dbo.Customers.DateCreated, 103) AS [DD/MM/YYYY], dbo.loyalty_points.LPoints, dbo.Orders.OrderID
FROM dbo.Customers INNER JOIN
dbo.loyalty_points ON dbo.Customers.CustomerID = dbo.loyalty_points.CustomerID INNER JOIN
dbo.Orders ON dbo.Customers.CustomerID = dbo.Orders.CustomerID
WHERE (CONVERT(char, dbo.Customers.DateCreated, 103) = CONVERT(char, GETDATE() - 6, 103))
I'am doing functionality test on DTS packages and saving my DTS packages to meta data services instead of saving them as local packages. We would like to see what information would be provided by saving them this way, but when we try to open the meta data browser (the 3rd icon under DTS) we get the following error:
An error occurred while trying to access the database information. The msdb database could not be opened.
I actually work in an organisation and we have to find a solution about the data consistancy in the database. our partners use to send details to the organisation and inserted directly in the database, so we want to create a new database as a buffer database to insert informations from the partners then make an update to the main database. is there a better solution instead of that?
View 6 Replies View RelatedIn Code Behind, What is proper select statement syntax to retrieve the @BName field from a table?Using Visual Studio 2003SQL Server DB
I created the following parameter:Dim strName As String Dim parameterBName As SqlParameter = New SqlParameter("@BName", SqlDbType.VarChar, 50) parameterBName.Value = strName myCommand.Parameters.Add(parameterBName)
I tried the following but get error:Dim strSql As String = "select @BName from Borrower where BName= DOROTHY V FOWLER "
error is:Line 1: Incorrect syntax near 'V'. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: Line 1: Incorrect syntax near 'V'.
Source Error:
Line 59: Line 60: Line 61: myCommand.ExecuteNonQuery() 'Execute the query
Time Price Volume
090048 510 6749
090122 510 2101
090135 510 1000
090204 505 2840
090213 505 220
090222 505 1260
090232 505 850
090242 505 200
090253 510 1200
090313 510 570
090343 510 250
090353 510 160
Criteria
Retrieve records with independent price and its total volume per minute
SELECT SUBSTRING(st,1,4) AS Ttime,d_price AS Price,SUM(l_cum) AS Volume FROM cmd4
WHERE sd='20060717' AND serial='0455'
GROUP BY SUBSTRING(st,1,4),d_price,l_cum
Result of the above query: -
Time Price Volume
0900 510 6749
0901 510 1000
0901 510 2101
0902 505 200
0902 505 220
0902 505 850
0902 505 1260
0902 505 2840
0902 510 1200
0903 510 160
0903 510 250
0903 510 570
THE FOLLOWING RESULT STILL NOT A TOTAL FOR A MINUTE]
E.G
0901 510 1000
+
0901 510 2101
=
0901 510 3101 <- I NEED THIS
Can any one advice or give me tips over this. Please
I'm having a weird problem with an easy process. I have a section that allows people to enter their name and email address if they wish to be contacted. I have it set up so that when they enter that information into the text boxes, the info is then sent to my SQL database. The code looks right to me, but it never comes up. Here is the code I have for the button_click.
Dim NTAdatasource As New SqlDataSource()
NTAdatasource.ConnectionString = ConfigurationManager.ConnectionStrings("DatabaseConnectionString1").ToString()
NTAdatasource.InsertCommandType = SqlDataSourceCommandType.Text
NTAdatasource.InsertCommand = "INSERT into ContactUs (YourName, EmailAddress, DateTimeStamp) VALUES (@YourName, @EmailAddress, @DateTimeStamp)"
NTAdatasource.InsertParameters.Add("YourName", txtYourName.Text)
NTAdatasource.InsertParameters.Add("EmailAddress", txtEmailAddress.Text)
NTAdatasource.InsertParameters.Add("DateTimeStamp", DateTime.Now)
Dim RowsAdded As Integer = 0
Try
RowsAdded = NTAdatabase.Insert()
Catch ex As Exception
Server.Transfer("problem.aspx")
End Try
If RowsAdded <> 1 Then
Server.Transfer("problem.aspx")
Else
Server.Transfer("success.aspx")
End If
End Sub
The strange thing is that when I debug, the "rowsadded" value comes up to 1. The process runs through debugging just fine and redirects me to my "success.aspx" page. What am I doing wrong?
Thanks
I work with multiple servers and I have system administrator rights on all server. My question is, why on some server when I click on a database it shows the General, Table & Index, Space Allocated tool bar, than if I go to a different server, click a database it does not show? I would like to see this toolbar on all my servers when I click a database.
Thanks Reggie
I want to write a sql query for information from a table in different database but on the same server.
what is the syntax.
select * from databse2.owner.table ??
i am in application1 database and i want to query from application2 database on the same server.
use application1
go
Select * from application2.dbo.tablename
go
Is this query correct?
Pardon the possibly senseless question - I have been an NT Administrator for some years, and have just gotten thrown into picking up some SQL DBA work and I'm still feeling my way around.
Is it possible to query the Master database for the setup information (etc) on the other databases? Several have been marked suspect due to a hard drive failure and I am trying to figure out what the original setup of the databases was.
Thanks,
Mary Elizabeth
NW Natural
Hello,I am trying to use the SQl Server database reporting tools to create areport on the database to find areas for optimization including tableformats, column formats, and sizes. Can anybody please help me withthis. If any body has used any other tool by which I can create abovementioned reports plz do let me know. I will be really thankful foryour help.ThanksSuneel
View 1 Replies View RelatedI have a serious problem with either SQL Server or SQL Reporting Services. I have 7 tables in a database that are relational. I have created serveral reports against this database. Last week when I pulled one of the reports, I was missing information on one of the cells. The reason why I know this is because my boss has us verify the report against the database. That took about an hour to complete. When we found that the report was not correct I repulled the report and the cell that was empty, was now populated. What gives? This is the same report I have pulled for the last month and had no problems until now. Is there a time issue involved between the last update to the database to the time I pull the report? Or maybe there is something wrong with the query?
SELECT Lease.[Lease #], MineralContacts.[Mineral Owner First Name], MineralContacts.[Mineral Owner Last Name], Tract.County, Tract.[Tract Number],
Tract.[Tract Description], Tract.Section, Tract.Block, Tract.Survey, Tract.Abstract, Lease.[Lease Date], Tract.[Title Check Though], Tract.[2ndTitleReview],
Tract.[2nd R/S Complete], Tract.[Title Agent], Tract.[Tract Gross Acres], SubTract.[Tract Net Acres], Draft.[Draft Status], Draft.[Draft Due Date],
Draft.[Draft Amount], Draft.[Draft #], Draft.[ANB Invoice #], Draft.[Lse File Sent to CP], Draft.[Money Wired from CP to EA],
Draft.[STA Approved Draft for pmt], Draft.[STA Recommend to Return Daft], Draft.[KE Advised ANB to Pay Draft], Draft.[KE Advised ANB to Return Draft],
Draft.[Paid Draft Recd], SubTract.Comments, SubTract.Hide
FROM Tract INNER JOIN
SubTract ON Tract.TractID = SubTract.TractID INNER JOIN
Mineral ON SubTract.SubTractID = Mineral.SubTractID INNER JOIN
Lease ON Mineral.MineralID = Lease.MineralID INNER JOIN
Draft ON Lease.LeaseID = Draft.LeaseID INNER JOIN
MineralContacts ON Mineral.MineralID = MineralContacts.MineralID
WHERE (Draft.[Draft Status] IS NOT NULL) AND (Draft.[Draft Due Date] IS NOT NULL)
This is probably a very simple question but i am having problems with inserting information into database
The function takes the values "FirstName" And "LastName" from A table Called "Customer" and the value "ProductID" from a table called "Products" and inserts them into a table called " NewOrder".
Everything compiles ok but when I press the button the information is not uploaded to thedatabase. ( There is no error message)
This is the stored procedure
CREATE PROCEDURE SP_NewOrder(@CartID char (36), @CustomerID Varchar (50))AS
INSERT INTO NewOrder (FirstName, LastName, ProductID)
SELECT Customer.FirstName, Customer.LastName, Products.ProductID
From Customer,Products Join ShoppingCart ON Products.ProductID =ShoppingCart.ProductIDWHERE ShoppingCart.CartID = @CartID AND Customer.CustomerID = @CustomerIDGO
This is the Function
Public Shared Function CreateOrder() AS String Dim customerID As integer Dim connection as New SqlConnection(connectionString) Dim command as New SqlCommand("SP_NewOrder",connection) command.CommandType = CommandType.StoredProcedure command.Parameters.Add("@CartID", SqlDbType.Char,36) command.Parameters("@CartID").Value = shoppingCartID command.Parameters.Add("@CustomerID", SqlDbType.Varchar,36) command.Parameters("@CustomerID").Value = customerID Try
connection.Open() command.ExecuteNonQuery() Finally connection.Close()
End TryEnd Function
This is the page code
Sub btn3_Click(sender As Object, e As EventArgs)Dim cart as New ShoppingCart()Dim shoppingCartID As IntegerDim customerID As Integer = Context.Session("worldshop_CustomerID")cart.CreateOrder()End Sub
Can Anyone see where I am going wrong
Many thanks
martin
I would like to know how to (if it is at all possible) in SQL (or even ADO) to retrieve all the data concerning database X's
a) Tables
b) Tables column names
c) Tables column's data types
I don't want to use the doa.tabledefs object.. .i would prefer to do it in SQL, but using ADO objects (since I am developing stuff in VB) would be ok too.
Please Help.. i have been going crazy trying to find anything useful.. email me back please!
Thanks,
Matt
Hi,
I want to get information about all the indexes in a database.
Can any one suggest me a query for this.
Thanks.