Hello,
I am using datareader to retrive the varchar (max) value from the database. But it is reteriving only 8000 why ? I am not using normal varchar datatype.
Below is the sample code.
Convert.ToString(_oDr["MyVar"])
where _oDr is the datareader
MyVar is defined of type varchar (max) in the sql 2005 database.
Any help will be appreciated.
I try to retrive an xml portion (<points><point><x>1</x></point></points>) stored in a varchar(max) column, this is my code dr = cmd.ExecuteReader();
_xmlFile = dr.GetSqlString(dr.GetOrdinal("XmlJoin")).ToString();
Label1.Text = _xmlFile; and this is what I get "12"Maybe I missed something to get the whole XML String
Hi friends
I have a bit problem here
Just I want to get back all deleted record of database
How do I perform this task?
If It is possible then plz help me out?
Thanks in Advance
Khan
private void Page_Load(object sender, System.EventArgs e){ // Put user code to initialize the page here MemoryStream stream = new MemoryStream (); SqlConnection connection = new SqlConnection (@"..."); try { connection.Open (); SqlCommand command = new SqlCommand ("select Picture from Image", connection); byte[] image = (byte[]) command.ExecuteScalar (); stream.Write (image, 0, image.Length); Bitmap bitmap = new Bitmap (stream); Response.ContentType = "image/gif"; bitmap.Save (Response.OutputStream, ImageFormat.Gif); } finally { connection.Close (); stream.Close (); }
Error: byte[] image = (byte[]) command.ExecuteScalar ();
Unable to cast object of type 'System.Int32' to type 'System.Byte[]'.
i am storing gender in the database.i want to retrive it in one of the radiobuttons for male and female already present on the form . how can i?
View 1 Replies View RelatedHi, Assume I have a table name "myTime". This table is simply only have 1 (one) DATETIME field "MyTestTime" (also serve as a primary number).Table MyTime- MyTestTime : SQLTYPE DATETIMETo insert a new row into this field, I simply wrote :SqlCommand sqlCommand = new SqlCommand("insert into MyTime values('2006-01-09')", sqlConnection);
I got the value of "2006-01-09" from a textbox or other relevan control.I realize when I try to use "SELECT * FROM MyTime" statement, MSSQL server 2000 automatically convert my date value from "2006-01-09" to "01/09/2006" (from YYYY-MM-DD to MM/DD/YYYY). I don't know why this one must be converted to MM/DD/YYYY automatically (I believe this behavior is depend on some "setting option" in my MSSQL server - but I don't know which one).The challenge is :In my country, the actual date format is like German Date format (DD-MM-YYY). Well I know this is only "Customization" problem. But how insert datetime value given from sql query to a datetime variable?// Connect to database, make a query, get the datareader result, and bla bla blaDateTime aDateTime = new DateTime;aDateTime = Convert.ToDateTime(myDataReader["PostDate"].ToString());// close connectionMy question isHow can I make sure that aDateTime's day is 09 not 01. How my program know that 09 is day not month. I can't use string.split() method because it's possible that my database setting will change from "mm-dd-yyyy" to "dd-mm-yyyy"thanks
Is it possible for retriving data from a remote Sql server database and storing it in a local sqlserver database.
View 1 Replies View Related I would appreciate help with retriving the ID of the last record inserted. Have spent considerable time in forums and google but can't find anything that works for me.
Here is my VB Code
Dim queryString As String = "INSERT INTO [DUALML] ([UseriD], [Company]) VALUES (@UseriD, @Company)" Dim dbCommand As System.Data.IDbCommand = New System.Data.SqlClient.SqlCommand dbCommand.CommandText = queryString dbCommand.Connection = dbConnection
Dim dbParam_useriD As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter dbParam_useriD.ParameterName = "@UseriD" dbParam_useriD.Value = useriD dbParam_useriD.DbType = System.Data.DbType.Int32 dbCommand.Parameters.Add(dbParam_useriD) Dim dbParam_company As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter dbParam_company.ParameterName = "@Company" dbParam_company.Value = company dbParam_company.DbType = System.Data.DbType.[String] dbCommand.Parameters.Add(dbParam_company)
Dim rowsAffected As Integer = 0 dbConnection.Open Try rowsAffected = dbCommand.ExecuteNonQuery Finally dbConnection.Close End Try
Return rowsAffected End Function
every time i try to get data of a student stored in SQL server 2000 in the student table it gives me an error
Login failed for user '???????????ASPNET'
Exception Details: System.Data.SqlClient.SqlException: Login failed for user '??????????ASPNET'.
works fine normally its only when i try to reteive actuall data stored on the server. theres obviously something wrong the server side.
help help help
Hi All.
Is there a way to retrieve the position of a word, phrase or sign in a field?
For example, Field content is ABCDEFG1239/1002STJ
I would like to get the exact position of / which will be position 12.
Thank you.
Best regards
Baby writes "how to retrive data from four table
four tables have same column number and name
i am trying to retrive one column data from all the table
i am trying these :- to get the result
select mutual_fund.customer_id,insurance.customer_id,fixed_dep.customer_id,home_loan.customer_id from mutual_fund,insurance,fixed_dep,home_loan where (mutual_fund.customer_id=fixed_dep.customer_id and mutual_fund.customer_id=home_loan.customer_id and fixed_dep.customer_id=home_loan.customer_id) or (mutual_fund.customer_id=fixed_dep.customer_id and mutual_fund.customer_id=insurance.customer_id and fixed_dep.customer_id=insurance.customer_id) or (mutual_fund.customer_id=home_loan.customer_id and mutual_fund.customer_id=insurance.customer_id and home_loan.customer_id=insurance.customer_id) or (fixed_dep.customer_id=home_loan.customer_id and fixed_dep.customer_id=insurance.customer_id and home_loan.customer_id=insurance.customer_id)
the comparision working in the query
please help me tell me how to solve my problem"
Hi
I am trying to retrive data from table store in .sdf database file
but not able to do it.
where i will use select * from xyz in project. I am developing it on desktop
using vc++ 2005 in SQL server compact edition.
am using
hr = pICmdText->Execute(NULL, IID_NULL, param, NULL, NULL);
It is not easy for me to see data of table from sdf file
so plz reply
Hi Every one
I m using FCKeditor in my web application and my requirement is to create and edit documents in FCKeditor.
The problem is in storing the data. it is not storing the data in the DB. its only stors some html code but it is incomplete.
Please some one Solve this problem
i have a textbox which a user enters a numeric value
i want it to use SqlDataSource and check if the value exists in any of the tables.
in my text box the users would enter starting from '100000' or '200000'
i want it to check the view that starts the # with '100000' and 2ed view starts '200000'
With this i can check in one of the tables and make the selection.
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:imacstestConnectionString %>"
SelectCommand="SELECT [ReportNumber] FROM [AppraisalSummaryBlue] WHERE ([ReportNumber] = @ReportNumber)">
<SelectParameters>
<asp:ControlParameter ControlID="txtReport" Name="ReportNumber" PropertyName="Text"
Type="String" />
</SelectParameters>
</asp:SqlDataSource>
How can i make this possible ?
i was thinking putting a second sqldatasource and have that check the second view but how can i make the textbox goto the correct selectcommand ?
I have a text column in my db which stores more than 8000 characters. When I retrieve the values from the column in query analyzer (I have set the output buffer to 8000), it only shows me first 8000 chars only. How do I display all the text from the text field?
View 2 Replies View RelatedHi,
I am tring to figure out how to retrieve the value of a global variable from s SQL task, the value for the Global variable is set in a Active Script Task. Any help is greatly appreciated.
Thanks,
Satish.
Hi,
Can any one suggest me how to retrieve most recent job from msdb..sysjobhistory table?
I want to supply the job name which has more than 1 steps. Step 1 or more is already completed ( success/failure) and in the last step I am trying to retrieve sysjobhistory.messages(success/failure) stored in the sysjobhistory table for the steps already executed.
I want the records related with last/current job executed.
Thanks.
Hello,
I need some help on this.
I want to retrieve all the duplicate records from a particulat column.
For eg suppose i have a table named testtable
columns in the table : item_id,ref_no,title,address
now i need to check if there are any duplicate entries in the ref_no column and if there are any retrieve the records.
Gaurish Salunke
Software Developer
OPSPL
Hi Guys.
i have write a store procedure which take few input and then backup the database and at the same time it's restore the database with new name, but i m hving a error code.
what this program do in restore section, it's read the backup file and all give me list of all the file with the location and then i can rename them.
actually the purpose of doing this is to create a new database on behalf of old database. plz have alook code
PLZ, PLZ help me, it's really geting headach
USE [master]
GO
/****** Object: StoredProcedure [dbo].[CreateNewDB] Script Date: 01/28/2008 17:13:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[CreateNewDB]
@ActualDb varchar(128),
@dbname sysname ,
@recipients varchar(128)
AS
SET NOCOUNT ON
Declare @cmd sysname ,
@filename varchar(128) ,
@Backuppath varchar(1000),
@LogicalName varchar(2000),
@ActualPath varchar(2000),
@Aloop int,
@FileID int,
@sql nvarchar(4000)
SET @Backuppath = 'C:' + @dbname
-- TAKE BACKUP
BACKUP DATABASE @ActualDb TO DISK = @Backuppath WITH NOFORMAT, INIT, NAME = 'DBBackup-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
-- Get files in backup
select @cmd = 'restore filelistonly from disk = ''' + @Backuppath + ''''
CREATE table #RestoreFileListOnly
(
LogicalName sysname,
PhysicalName sysname,
type char(1),
FileGroupName sysname,
[size] bigint,
[MaxSize] bigint,
FileID int
)
INSERT into #RestoreFileListOnly
exec(@cmd)
-- buld the restore command
set @Aloop=1
set @FileID=0
set @sql= ''
set @sql = @sql + 'RESTORE DATABASE ' + @dbname + CHAR(10)
set @sql = @sql + ' FROM DISK = ''' + @Backuppath + '''' + CHAR(10)
set @sql= @sql + ' WITH FILE = 1' + CHAR(10)
WHILE (@aloop <= @@ROWCOUNT)
BEGIN
SELECT @LogicalName = LogicalName , @FileID = FileID, @ActualPath = Left(PhysicalName, len(PhysicalName)-charindex('',reverse(PhysicalName))+1) FROM #RestoreFileListOnly WHERE FILEID > @FileID
SET @sql= @sql + ',' + CHAR(10)
SET @sql= @sql + CHAR(9) + 'MOVE''' + @LogicalName + '''TO''' + @ActualPath + '''' + @dbname + ''''
-- @sql= @sql + 'MOVE '''+ + '' TO N'C:Program FilesMicrosoft SQL ServerMSSQL.2MSSQLDATAMALIK.mdf'
SET @Aloop=@Aloop+1
END
SET @sql = @sql + ', NOUNLOAD, STATS = 10'
-- Restore the database
print @sql
EXEC (@sql)
Drop table #RestoreFileListOnly
-- send email to the define person.
EXEC master..xp_sendmail @subject = @cmd, @recipients = @recipients, @message = @@servername
ERROR:
Msg 213, Level 16, State 7, Line 1
Insert Error: Column name or number of supplied values does not match table definition.
Msg 3013, Level 16, State 1, Line 1
RESTORE FILELIST is terminating abnormally.
hi guys i am new to asp.net
i have been using asp2.net (c#) to retrive infomation from my sql server 2005 database
the connection infomation is within the web.config
now i need to retreive an infomation from the sql server 2005 database instread of the whole thing
example:
select name from student where nric = 'asd'
(only 1 result shld come out)
could anyone show me the general coding of what i should do to make a variable store one targeted result of the database please?
Hi
While trying to run a query from SQLServer 2005 on linked server (Oracle 10G) using Open query, I receive followin error message:
Msg 7346, Level 16, State 2, Line 1
Cannot get the data of the row from the OLE DB provider "OraOLEDB.Oracle" for linked server "EDWORA".
It runs for some hours before giving the mentioned error.
My query is something like :
select * from openquery (EDWORA, 'select * from Tablex')
Any idea how can I solve this issue?
Regards
Elham
Dear Friends,
Hi I am having a problem while showing Products from SQL
Server database. Exactly I have a table named Products which has some Products
posted by admin I want to show some latest Products on my default page
based on DateTime which are displayed like show last week articles,show last two weeks articles etc on button click I written my SQL query like: searchquery = "select * from Prducts where Date between '" + System.DateTime.Now.AddDays(-7).ToShortDateString() + "' and '" + System.DateTime.Now.AddDays(-1).AddHours(23).AddMinutes(59).AddSeconds(29).ToShortDateString() + "'"; If any body knows plz help me
I have looked far and wide and have not found anything that works to allow me to resolve this issue.
I am moving data from DB2 using the MS OLEDB Provider for DB2. The OLEDB source sees the column of data as DT_TEXT. I setup a destination to SQL Server 2005 and everything looks good until I try and run the package.
I get the error:
[OLE DB Source [277]] Error: An OLE DB error has occurred. Error code: 0x80040E21. An OLE DB record is available. Source: "Microsoft DB2 OLE DB Provider" Hresult: 0x80040E21 Description: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".
[OLE DB Source [277]] Error: Failed to retrieve long data for column "LIST_DATA_RCVD".
[OLE DB Source [277]] Error: There was an error with output column "LIST_DATA_RCVD" (324) on output "OLE DB Source Output" (287). The column status returned was: "DBSTATUS_UNAVAILABLE".
[OLE DB Source [277]] Error: The "output column "LIST_DATA_RCVD" (324)" failed because error code 0xC0209071 occurred, and the error row disposition on "output column "LIST_DATA_RCVD" (324)" specifies failure on error. An error occurred on the specified object of the specified component.
[DTS.Pipeline] Error: The PrimeOutput method on component "OLE DB Source" (277) returned error code 0xC0209029. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.
Any suggestions on how I can get the large string data in the varchar column in DB2 into the varchar(max) column in SQL Server 2005?
I am trying to create a store procedure inside of SQL Management Studio console and I kept getting errors. Here's my store procedure.
Code Block
CREATE PROCEDURE [dbo].[sqlOutlookSearch]
-- Add the parameters for the stored procedure here
@OLIssueID int = NULL,
@searchString varchar(1000) = NULL
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
IF @OLIssueID <> 11111
SELECT * FROM [OLissue], [Outlook]
WHERE [OLissue].[issueID] = @OLIssueID AND [OLissue].[issueID] = [Outlook].[issueID] AND [Outlook].[contents] LIKE + ''%'' + @searchString + ''%''
ELSE
SELECT * FROM [Outlook]
WHERE [Outlook].[contents] LIKE + ''%'' + @searchString + ''%''
END
And the error I kept getting is:
Msg 402, Level 16, State 1, Procedure sqlOutlookSearch, Line 18
The data types varchar and varchar are incompatible in the modulo operator.
Msg 402, Level 16, State 1, Procedure sqlOutlookSearch, Line 21
The data types varchar and varchar are incompatible in the modulo operator.
Any help is appreciated.
Hi,
I have an ASP.NET application that uses VARCHAR extensively in the tables and, more importantly, stored procedures (a couple hundred of them).
This app needs to start accepting foreign language in some areas, so I was wondering if there was some way to go through the tables and, more importantly, the stored procedures and change all "VARCHAR" references to "NVARCHAR" ?
Are the stored procedures stored as a text file somewhere on the server? If so I could use some sort of "replace" software utility to go through and change all VARCHAR to NVARCHAR
thanks!
-Bret
For the life of me I cannot figure out why SSIS will not convert varchar data. instead of using the table to table method, I wrote a SQL query so that I could transform the datatype ntext to varchar 512 understanding that natively MS is going towards all Unicode applications.
The source fields from Access are int, int, int and varchar(512). The same is true of the destination within SQL Server 2005. the field 'Answer' is the varchar field in question....
I get the following error
Validating (Error)
Messages
Error 0xc02020f6: Data Flow Task: Column "Answer" cannot convert between unicode and non-unicode string data types.
(SQL Server Import and Export Wizard)
Error 0xc004706b: Data Flow Task: "component "Destination - Query" (28)" failed validation and returned validation status "VS_ISBROKEN".
(SQL Server Import and Export Wizard)
Error 0xc004700c: Data Flow Task: One or more component failed validation.
(SQL Server Import and Export Wizard)
Error 0xc0024107: Data Flow Task: There were errors during task validation.
(SQL Server Import and Export Wizard)
DTS used to be a very strong tool but a simple import such as this is causing me extreme grief and wondering of SQL2005 is ready for primetime. FYI SP1 is installed. I am running this from a workstation and not on the server if that makes a difference...
Any help would be appreciated.
Can anyone help me on this!I've got more than a 1000 records in a SQL server database.The problem is that the the date field is set to varchar, and that gives a lot of trouble. (for example by sorting a table, it's a mess)How can i make sure that i will have a table with the date field set to datettime en that those 1000 records still will be in it. thanks in advance!
View 1 Replies View RelatedI have an encrypted column of data that is encrypted by a passphrase. The passphrase was encrypted by a symetric key in a key pair. The passphrase also is stored in a table. I can get the passphrase as needed to encrypt/decrypt the columns. I copied the production database to a new database for development. Subsequently I had to create a new symmetric/asymmetic key pair and recreated my passphrase with the new key pair. Now the passphrase will decrypt a text column but it will not decrypt two other columns which are of type varchar in the database. Here is an example:
DECLARE @pss varchar(30)
EXEC [dbo].[uspPassPhraseGet] @pss OUTPUT
SELECT DISTINCT contactid, uissueid, createdby, created_dt
,CONVERT(varchar(max),DecryptByPassPhrase(@pss, CONVERT(varchar(max),dbo.tbl_msg_app_legislativeinquiry.title), 1, CONVERT(varbinary, 23))) as title
,CONVERT(varchar(max),DecryptByPassPhrase(@pss, CONVERT(varchar(max),dbo.tbl_msg_app_legislativeinquiry.description), 1, CONVERT(varbinary, 23))) as description
,CONVERT(varchar(max),DecryptByPassPhrase(@pss, CONVERT(varchar(max),dbo.tbl_msg_app_legislativeinquiry.shortdesc), 1, CONVERT(varbinary, 23))) as shortdesc,
closed_dt, confidential, statusid, due_dt, deleted_dt,deletedbyid, highrisk, dbo.tbl_msg_app_legislativeinquiry.designator, dbo.tbl_ref_sys_status.description AS statusdesc
FROM dbo.tbl_msg_app_legislativeinquiry INNER JOIN
dbo.tbl_ref_sys_status ON statusid = dbo.tbl_ref_sys_status.ustatusid INNER JOIN
dbo.tbl_gbl_lkp_security ON uissueid = dbo.tbl_gbl_lkp_security.msgid AND
dbo.tbl_msg_app_legislativeinquiry.designator = dbo.tbl_gbl_lkp_security.designator
Like I said I can execute the uspPassPhraseGet stored procedure and I get my passphrase. It will correctly decrypt the dbo.tbl_msg_app_legislativeinquiry.description field which is great but the other two fields will not decrypt. When i copied the database over the encrypted fields do not display the same on the new database. The old database shows a box character followed by a bunch of junk (as expected). The new copied table on the new database shows only a single box (not the same as the original). Is there a known bug with copying a table with varchar fields that are encrypted to a new database? I tried to run a test and got the same result. I also tried to convert the varchar columns to text to see if that solved the problem and it didn't. The description field however is a text type column and it reads exactly as the original. The problem I think is that the Copy Database didn't actually copy my data correctly. How can I get the original encrypted data from the production into my development. I also tried just dropping the table and reimporting the table but that didnt take either. Scratching my head on this one.
Hi all,
I'm trying to get an understanding of a serious problem I have with a large DB in production. This is going to be obvious to someone (everyone probably) <bg>
I have a table which consists of numerous varchars and ints but also a Text type field. This table resides in a SQL 2000 Database. This DB currently has a data file size of 16Gb and a Transaction Log size of 17Gb. When I edit the table and increase the size of a Varchar field from 50 to 100 these files grow to more than double their size!
Why is this happening and how can I prevent this?
TIA
NozFx
I have a string which I need to know where it came from in a database.I don't want to spend time coding this so is there a ready made scriptwhich takes a string as a parameter and searches all the tables whichcontain varchar type columns and searches these columns and indicate whichtables contain that string?Full text search is not enabled.--Tonyhttp://dotNet-Hosting.com - Super low $4.75/month.Single all inclusive features plan with MS SQL Server, MySQL 5, ASP.NET,PHP 5 & webmail support.
View 1 Replies View Related
I have a table that contains a lot of demographic information. The data is usually small (<20 chars) but ocassionally needs to handle large values (250 chars). Right now its set up for varchar(max) and I don't think I want to do this.
How does varchar(max) store info differently from varchar(250)? Either way doesn't it have to hold the container information? So the word "Crackers" have 8 characters to it and information sayings its 8 characters long in both cases. This meaning its taking up same amount of space?
Also my concern will be running queries off of it, does a varchar(max) choke up queries because the fields cannot be properly analyzed? Is varchar(250) any better?
Should I just go with char(250) and watch my db size explode?
Usually the data that is 250 characters contain a lot of blank space that is removed using a SPROC so its not usually 250 characters for long.
Any insight to this would be appreciated.
Dear All
I have a table with the following structure in sql server 2005
create table app(
sno int,
name varchar(50),
add varchar(50),
city varchar(50),
state varchar(50)
)
it contains the follwing data
------------------------------------------
sno name add city state
------------------------------------------
1 mark street no1 newcity newstate
2 mark street no1 newcity newstate
3 mark street no1 newcity newstate
4 mark street no1 newcity newstate
5 mark street no1 newcity newstate
6 mark street no1 newcity newstate
7 mark street no1 newcity newstate
8 mark street no1 newcity newstate
9 mark street no1 newcity newstate
10 mark street no1 newcity newstate
11 mark street no1 newcity newstate
12 mark street no1 newcity newstate
13 mark street no1 newcity newstate
14 mark street no1 newcity newstate
15 mark street no1 newcity newstate
16 mark street no1 newcity newstate
17 mark street no1 newcity newstate
18 mark street no1 newcity newstate
19 mark street no1 newcity newstate
20 mark street no1 newcity newstate
----------------------------------------
I want to retrive previous 5 records, next 5 records and the record that meet the where condition of a select query.
When I run
select sno,add,name,city,state from app where sno=7
I want the following result
------------------------------------------
sno name add city state
------------------------------------------
2 mark street no1 newcity newstate |
3 mark street no1 newcity newstate |
4 mark street no1 newcity newstate | -- previous 5 records
5 mark street no1 newcity newstate |
6 mark street no1 newcity newstate |
7 mark street no1 newcity newstate --- searched record
8 mark street no1 newcity newstate |
9 mark street no1 newcity newstate |
10 mark street no1 newcity newstate |--- next 5 records
11 mark street no1 newcity newstate |
12 mark street no1 newcity newstate |
----------------------------------------
if there is a method to get the above result set, kindly post the query.
hi all,
i have a deployed on the report server. is there any way i can retrive the metadata(xml) from report server using C#.
Also wanted to run the reoprt from server and get it's output (data) in XML.
please help.
Regards...
girija shankar