Data Access :: How To Retrieve Remote Database Table Value To String Variable

May 28, 2015

I am using C# in  Visual Studio 2008 and remote database as sql server 2008 R2. I want to read remote database table's field value and i have to move that read value to string variable. how to do it. 

And my code is :

string sql = "Select fldinput from tmessage_temp where fldTo=IDENT_CURRENT('tmessage_temp')";
SqlCommand exesql = new SqlCommand(sql, cn);
exesql.CommandType = CommandType.Text;
SqlDataReader rd1 = default(SqlDataReader);
rd1 = exesql.ExecuteReader();

View 6 Replies


ADVERTISEMENT

Retrieve Data From Table Type Variable

Dec 5, 2014

Can we retrieve data from table type variable as an array in oracle key values..

E.g.

ALTER PROCEDURE prn1( @p_prn as KeyValuePair readonly)
-- proc which having parameter table type as parameter
AS
declare @v_formatted_str varchar(50)
BEGIN
set @v_formatted_str = v_formatted_str + @p_prn(1)(1)
END

View 3 Replies View Related

Inserting Data Into A Table From A String Variable

Apr 25, 2008

How to insert data into a table from a string variable? Like below:


DECLARE @Data AS NVARCHAR(MAX)

SET @Data = 'bla|bla|bla
lab|lab|lab
abl|abl|abl'


BULK INSERT tablename

FROM @Data

WITH

(


DATAFILETYPE = 'char',

FIELDTERMINATOR = '|',

ROWTERMINATOR = ''

);

View 3 Replies View Related

Data Access :: MS Access ADODB Connection To Stored Procedure - Cannot Retrieve Data

Sep 22, 2015

I'm trying to re-write my database to de-couple the interface (MS Access) from the SQL Backend.  As a result, I'm going to write a number of Stored Procedures to replace the MS Access code.  My first attempt worked on a small sample, however, trying to move this on to a real table hasn't worked (I've amended the SP and code to try and get it to work on 2 fields, rather than the full 20 plus).It works in SQL Management console (supply a Client ID, it returns all the client details), but does not return anything (recordset closed) when trying to access via VBA code.The Stored procedure is:-

USE [VMSProd]
GO
/****** Object: StoredProcedure [Clients].[vms_Get_Specified_Client] Script Date: 22/09/2015 16:29:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON

[code]....

View 4 Replies View Related

Insert Information And To Database And Retrieve The ID Created For That Row Of Data In The Table

May 4, 2007

Hi,
 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

View 2 Replies View Related

SSIS OLEDB Source Data Access Mode (Table Name Or View Name Variable)

Apr 3, 2007

Thanks for any one can give me a help.

I am try to transfer some tables data from one database server into another database server. I create a package in SSIS, and I use a variable to pass each table name. In Data flow, I use a OLEDB Source, but I cannot set the Data access mode to Table name or view name variable. Ever time, I will get this following error info "===================================

Error at Data Flow Task [OLE DB Source [31]]: A destination table name has not been provided.

(Microsoft Visual Studio)

===================================

Exception from HRESULT: 0xC0202042 (Microsoft.SqlServer.DTSPipelineWrap)

------------------------------
Program Location:

at Microsoft.SqlServer.Dts.Pipeline.Wrapper.CManagedComponentWrapperClass.ReinitializeMetaData()
at Microsoft.DataTransformationServices.DataFlowUI.DataFlowComponentUI.ReinitializeMetadata()
at Microsoft.DataTransformationServices.DataFlowUI.DataFlowAdapterUI.connectionPage_SaveConnectionAttributes(Object sender, ConnectionAttributesEventArgs args)".

Some one can tell me what is the reason, or give me some examples.

Thanks in advance.

View 7 Replies View Related

How To Retrieve Value From A Table Type Variable?

Dec 19, 2007

 Hi Forum,I have a split function.CREATE FUNCTION dbo.Split2(@List nvarchar(2000),@SplitOn nvarchar(5)) RETURNS @RtnValue table (Id int identity(1,1),Value nvarchar(100)) AS BEGINWhile (Charindex(@SplitOn,@List)>0)Begin Insert Into @RtnValue (value)Select Value = ltrim(rtrim(Substring(@List,1,Charindex(@SplitOn,@List)-1))) Set @List = Substring(@List,Charindex(@SplitOn,@List)+len(@SplitOn),len(@List))End Insert Into @RtnValue (Value) Select Value = ltrim(rtrim(@List)) ReturnEND Courtesy p2p.wrox.com  It returns a TableHow to take the return into a temporary table?I want it inside an SP.Because i have a varchar field named occasionTime (varchar) in a tablea sample data inside it is "January,21,EveryWeek"In my SP i have to get either "January" and compare it to something or "21" or "EveryWeek" for comparison.How to do it?Regards,Naveen 

View 1 Replies View Related

Remote Access To Report Data Sources And/or The Report Server Database Is Not Supported In This Edition Of Reporting Services.

Apr 24, 2006

I'm using SQL Express with Advance Services & I get this error when I try to deploy my reports. Why do I get this error

An error has occurred during report processing. (rsProcessingAborted)

Cannot create a connection to data source 'EDPSYS'. (rsErrorOpeningConnection)

The feature: "Remote access to report data sources and/or the report server database" is not supported in this edition of Reporting Services. (rsOperationNotSupported)

Justin Song

View 4 Replies View Related

TSQL - Retrieve All Columns In My MS Access Table Less Two Of Them

Apr 6, 2008

Hi guys,
Working on a MS Access database, I have a table named "myTable" which contains several fields.
I just want to retrieve all the fields (columns) in the myTable, without retrieving Col1 and Col2
What should my SQL string be?

SELECT * (not Col1, Col2) FROM myTable

Thanks in advance for any help.
Aldo.

View 5 Replies View Related

Data Access :: Retrieve Schema Information Of Columns Of Tables

Sep 10, 2015

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)...

View 3 Replies View Related

This Feature Remote Access To Report Data Sources And/or The Report Server Database Is Not Supported In This Edition Of Report

Jun 16, 2006

SQL server 2005 express reporting problem.

error message:

This feature "remote access to report data sources and/or the report server database" is not supported in this edition of reporting service

I got this error message when I try to connect to database hosted in another PC running SQL server 2000.

Is it true that SQlL server Express can only use Local Database Engine to host the database?



View 5 Replies View Related

Data Access :: Remote Data Object In VB5 To Get Current Login User?

Jul 13, 2015

I'm using a legacy application built using VB5 and SQL Server 7. After recompiling it, and putting the database in SQL Server 2012. I want to access the current login user using the SQL function SUSER_SNAME().

This is the code.

Set rdoRes = goDatabase.Connection.OpenResultset("select suser_sname()")

But I'm unable to get the current user login in the application. If I write any other SQL statement instead of this, then it runs. But only this statement is not running. Is there any security reasons for this?

View 8 Replies View Related

Could The ADS Access Database Sychronizer Wizard Accomodate A Remote Database?

Jan 4, 2008



I've used the ADS sample program and it works well. I looked at the code and changed it to VB (sorry, I'm not biligual yet), and have tried to 'adjust' it so I can connect to a remote PC (XP running IIS) instead of the local PC using the workgroup information database (system.mdw). Any ideas of the connection string that could make the connection?..my intention is to RDA once a week between a mobile device (3g/gsm) and a 'poor man's' IIS web server.
I've tried everything I can think of, and I'm sure its possible since all I'm doing is changing the connection path.

View 1 Replies View Related

Data Access :: Grant A User Permissions To Only One Table In A Database?

Jul 28, 2015

How do I grant a user permissions to only one table in a database.  How would it affect him using our Main App which is NAV with regards to his user's permission in NAV

View 2 Replies View Related

Connection String For Remote Database Connectivity(SQL Server2000)

Jun 12, 2007

hai,
I am working on ASP.NET 2003 with SQLServer2000. My application requires to be connected to the Database which is there in my Headoffice.
My SQLServerName is "MyDBServerTestDB"
Database Name is "WebTO", UserID="******" and Password="******"
My Remote Server IP Address is "192.168.1.2" and its Static IP is "58.93.61.235"
I have specified the Connection string for my Remote Server as
"Provider=SQLOLEDB.1;Server=58.93.61.235TestDB;UID=******;PWD=******;Database=WebTO"
                               (or)
"Provider=SQLOLEDB.1;Server=58.93.61.235TestDB,1433;UID=******;PWD=******;Database=WebTO"           (1433 is the Port number of the Remote SQLServer)
but it is giving the error " SQL Server does not exist or access denied".
if i execute the same application at my Headoffice (Remote Server) by changing the Connection string as
"Provider=SQLOLEDB.1;Server=192.168.1.2TestDB;UID=******;PWD=******;Database=WebTO"
then, it is working fine.
Can anyone tell me where i went wrong or what i have to specify in my Connection string so that i can access my Remote Database Server.
Thanks in Advance,
Srinivas.

View 7 Replies View Related

Remote Database Access

May 14, 2007

Hi can anybody know how to connect remote database with asp.net application? Actually my database is in different machine (in different server, different location) so i want to connect that server. Every time I'm getting the fallowing message
 
An error has occured while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.
Thank you,
Nagu

View 4 Replies View Related

VB Cant' Access My Remote Database

Feb 8, 2007

Hi everybody

I have a remote SQLExpress instance running. I can connect to its databases with the SQL Server Management Studio Express and Visualstudio (Datasources) on a remote system and on a local system.

But as soon as I try to run or debug a vb application a system security error apears.

Thanks for you help

Ueli

View 6 Replies View Related

Remote Data Access

May 27, 2008

Hi,

I have an MS SQL Server 2000 database running on a local area network. The database contains product info that I would like to use to populate a web catelog being hosted off site.

Can someone point me in the right direct regarding common practices for using this product database, which is updated on a regular basis, to update my Web catelog?

I was thinking periodically the Web server could remotely query my product database for new entries (this does not need to be real time).

What do you think...

View 6 Replies View Related

String / Variable Problem - Dynamic Table Name

Jul 20, 2005

HiI'm grateful for any light you can shed on this!!I've to admit, it's an unusual design but I've multiple contact tables namede.g. i2b_ash_contact or i2b_ted_contact.'i2b_' and '_contact' are static but the middle part is dynamic.Storing all contacts in one table with an identifier of e.g. 'ash' or 'ted'for each record is not possible.Returning the value from the dynamic Query is no problem but I don't knowhow to assign it to a variable.When I try doing this it either runs into problems with evaluating thevariables or doesn't retuen anything at all e.g. if I say at the end 'Print@AddressID'. The variable remains empty.How can I do something like:DECLARE@AddressID int,@ProgClient (varchar(10),@Table varchar(10)Note: @Prog is a string e.g. 'ash' or 'ted'SET @Table = 'i2b_ + @ProgClient + '_contactSET @AddressID = (SELECT AddressID FROM @Table WHERE ContactID = @ContactID)

View 2 Replies View Related

Remote Aspnet Database Access Help

Apr 18, 2008

I have my normal SQL 2005 database....and another database (on the same machine) that is the default aspnet database. I use that for authentication of course. Everything is working well except now I need to (for a SP I'm creating) access the UserID field in the Users table (without the customer logged in). I know this isn't the best way to do it but....Can someone tell me how to programatically (from an existing SP) access the aspnet database? So I can just check something against the UserID (providerkey) I already have? The customer isn't logged in at this point. I have their UserID from somewhere else. Here is what I am trying to use now. IT Works in Query Manager but throws an error (...in this security context) when I run it in ASP.NET...SELECT @UserName=(select UserName from  [aspnetdb].dbo.aspnet_Users where UserID=@UserID)   Thanks. 

View 1 Replies View Related

How To Access The Remote SQL Server Database

Apr 5, 1999

Hello SQL guys,

If possible to connect our remote server from my end ?.what r the possiblities is there.tell me some ideas,i already tried it to register our remote server database thru ODBC but it not connected properly but i ve tried in our intranet/network it's working fine.

Nellai

View 1 Replies View Related

Remote Access To Sql Database...permissions

Jul 23, 2005

Hi all,I need some help to access an SQL db on another machine. I am using VB.NETand remoting to make a client/server connection...although I don't thinkthis is relevant to the question.I have been asked to help with a small db project that will reside on ouroffice server and have approx. 5 users.I have installed a copy of MSDE on my development machine and plan on doingthe same on my office 'server'....in fact it may well already be on there. Ialso have another testbed to act as the client.Because I don't really know what I'm doing...........I have copied the dbcreated on my client to the server. I have done this by 'cheating'. I usethe VB.NET IDE to create the correctly named db under the sql server. I thengo to this file in windows explorer and write over it (and the .LDF) withthe file from the client.When I try to connect to this db remotely I get a fail to connect errorabout not recognising 'localmachinenameguest'...sorry, not the exact errorwording. I can provide this if it will be helpful.Can anyone point me in the right direct?I don't have any sql/db tools other than anything that is included with MSDE(which I don't think is much).I will be posting other Q's for any experts out there!Thanks in advance.Phil

View 4 Replies View Related

Sql Server Remote Database Access

May 17, 2007

if i can somehow load/copy a sql directory with mdf etc on pcs shipped to our customers with our app, but sql isnt installed/running on the shipped pc, can our customers' sql instances running on their existing machines "see/use" our shipped database, assuming the hard drive on the shipped pc can be made to look like a mapped/remote drive? We wonder about 2000 and 2005.

View 3 Replies View Related

How Can I Access To Remote Mysql Database?

May 5, 2008



Hi,

I am using Mysql ODBC 3.51 and/or 5.1 connection . this works fine with DTS But it hangs when I use with SSIS ...
MS Visual Studio is giving message like " MS Visual Studio is Busy"

Which driver should I use for SSIS?

thanks,
J

View 3 Replies View Related

SSIS String Variable Is Truncating Data

Sep 25, 2006

Greetings,


I have written a SSIS package which does the following:


- An 'Execute SQL Task' uses an OLEDB connection to execute a Stored Procedure in SQL Server 2005
A SSIS user defined variable named @SourceSQLStatement is passed to the Stored procedure as an OUTPUT
This variable is a string Data Type

- The Stored Procedure generates an SQL command and saves it in the variable
This variable is defined as @SourceSQLStatement nvarchar(4000) OUTPUT
The varible is then returned to the SSIS package


When the package is executed the variable does not contain the entire SQL command.
When I check in the Locals window during Debug, the contents seem to have been truncated:


+ User::SourceSQLstatement {SELECT a.FailureID, a.ExceptionHandlerID, a.FailureTypeCd, a.AccountNbr, a.AccountNm, a.CaseNbr, a.CustNm, a.AssociateNm, a.TargetSysNm, a.FailureDt, a.FailurePointTxt, a.SubmittedByNm, a.ErrorMsgTxt, a.CreateId, a.CreateDt, a.UpdateId, a.UpdateDt FROM GSPIntegrationException a WHERE a.CreateDt >= (select max(ETLC.RunDt) from X141572_ETLSTAGING.dbo.ETL ETL inner join X141572_ETLSTAGING.dbo.ETLControl ETLC on ETL.ETLID = ETLC.ETLID inner join X141572_ETLSTAGING.dbo.ETLRun ETLR on ETLC.ETLID = ETLR.ETLID an} String


The fully generated SQL command does not exceed the 4000 character length provided in the stored procedure.
Has anyone encountered this issue before?

Any help would be greatly appreciated

Thank You

View 4 Replies View Related

Remote Data Access (RDA.Pull)

Oct 19, 2006

Hi,

I am trying to use RDA.pull to pull data from SQL server 2000 to sql mobile, but it fails when some columns are included in the SQLSelectString, though the datatypes involved are only char, float, smallint and smalldatetime, and nothing is longer than 30. Since there is a very large number of columns, it is difficult to isolate the guilty one by trial and error! Coud someone please help?

- Nag Rao

View 1 Replies View Related

How To Create Trigger Which CREAT TABLE From A Variable String?

Feb 23, 2006



I have a Table Name "Forums". I want to ceate an AFTER-Trigger on it. It will execute when ever a new row is inserted to "Froums" Table.

Here is what I did but It needs to be corrected:

::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::

ALTER TRIGGER CreateTopicsTableTrigger

ON dbo.Forums

AFTER INSERT

AS

SET NOCOUNT OFF

DECLARE @myNewForum varchar

CAST(@@ROWCOUNT as varchar) /*Is it OK???*/

SET @myNewForum=@myNewForum+@@ROWCOUNT /*Here I dont know how assigments work in SQL*/

GO

CREATE Table @myNewForum /*Will this work some how???*/

( TopicID int IDENTITY NOT NULL, TopicTitle varchar(50) , CreatedBy varchar(50) ,

DateCreated DateTime , DateLastUpdate DateTime , LastUpdateBy varchar(50) )



::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::

View 5 Replies View Related

How Can I Retrieve Data From One Table To Another

Jan 12, 2006

Hi, everione,

I am having a big problem, trying to create datebase.
I have 3 tables: SUPERAVATAR, MASTERAVATAR, MEGAAVATAR.
- SuperAvatars are heroes in an online role playing gaming. They have an ID, ‘superavatarID’ which contain an UNIQUE NUMBER NOT NULL PRIMARY KEY to identify them.
A wisdom – ‘trickster’,’conjuror’,’magician’, etc..
A current owner… who is the user or player of the game and has that Super Avatar– we associate the link to USERID to know the person.
SuperAvatars can be fathers or mothers of Mega Avatars.
-MegaAvatars are the children of SuperAvatars…. They also have an ID UNIQUE NUMBER NOT NULL PRIMARY KEY to indentify them.
A magic power – it can be a ‘Leader’ or ‘Sheep’…
A ‘parent’ – parent is the number identifying to know to who Super Avatar belongs.
e.g.
SUPERAVAT WISD CURRENTOWNER FATHEROF MOTHEROF
1 Thick 3 1
2 Mentally Ch. 11 3
3 Smart 9 2
4 Genius 16 4
5 Thick 19



MEGAAVATARID MAGICPOWER PARENT
1 MAGICIAN 1
2 WIZARD 3
3 SORCERER 2
4 MAGICIAN 4

-We see that MEGAAVATAR 1 has a magic power as Magician and his father is ‘1’. ‘1’ identifies the SUPERAVATAR.
We see SUPERAVATARID…. who has the number ‘1’? the first in the row… who has wisdom – thick and he belongs to the USER with ID number 3.
-We see MEGAAVATARID… we choose the number 2…. His magic power is as WIZARD… and his father is the number 3.
We see SUPERAVATARID now… we look up the SuperavatarID 3…. We can see he has a wisdom – GENIUS… who belongs to the USERID 16 and he is father of MEGAAVATAR number 2.
The list can carry on and never stop.

I have this Problems:
We create in this example 3 tables: Users, SuperAvatar, MegaAvatar

SQL
CREATE TABLE users(userID NUMBER CONSTRAINT pk_user PRIMARY KEY,email VARCHAR2(50) NOT NULL UNIQUE,password VARCHAR2(15) NOT NULL UNIQUE,subscription CHAR(8) NOT NULL CHECK (subscription IN('ACTIVE' , 'INACTIVE' ) ) );

CREATE TABLE superavatar(superavatarID NUMBER CONSTRAINT pk_superavatar PRIMARY KEY, wisdom VARCHAR2(19) NOT NULL CHECK (wisdom IN ('THICK', 'MENTALLY CHALLENGED', 'AWAKE', 'SMART', 'GENIUS')), currentOwner NUMBER NOT NULL, fatherOf NUMBER,motherOf NUMBER);

CREATE TABLE megaavatar (megaavatarID NUMBER CONSTRAINT pk_megaavatar PRIMARY KEY, magicPower VARCHAR2(12) NOT NULL CHECK (magicPower IN('TRICKSTER','CONJUROR','MAGICIAN','WIZARD','SORCERER')), parent NUMBER);

Now, the 3 tables are created…..
What happen when we try to insert values to this table?
In this case we insert to User Table some examples:
INSERT INTO users VALUES('3','john@hotmail.com','great78','ACTIVE');
INSERT INTO users VALUES('9','chrisandsandra@gmail.com','chrisandra)','ACTIVE');
Now, we insert to SuperAvatar some examples;
INSERT INTO superavatar VALUES('1','THICK','3','1','');
INSERT INTO superavatar VALUES('3','SMART','9','3','');
|
|
‘9’ is the UserID that we already insert to the User table
What’s the problem?
We have to insert manually the data from USERID to CURRENTOWNER as we didn’t match or link CURRENTOWNER from SUPERAVATAR Table to USERID from USER Table with a SQL CODE.
What happen if we have thousands of USERS that they register to this game…? We will never know to how belongs that SUPERAVATAR but if someone do it manually can spend a year.

I am trying to fix this problem …. I add in SUPERAVATAR TABLE ‘CHECK’ in currentOwner..

SQL> CREATE TABLE superavatar
(superavatarID NUMBER CONSTRAINT pk_superavatar PRIMARY KEY, wisdom VARCHAR2(19) NOT NULL CHECK (wisdom IN ('THICK', 'MENTALLY CHALLENGED', 'AWAKE', 'SMART', 'GENIUS')),
currentOwner NUMBER NOT NULL CHECK (currentOwner IN(SELECT userID FROM users)),
fatherOf NUMBER,
motherOf NUMBER);
*
ERROR:
ORA-02251: subquery not allowed here

It doesn’t work.

Please HELP, I have exam tomorrow

thank you
Desy

View 1 Replies View Related

How To Access Remote Connections For A Local Database?

May 5, 2008



Hi 2 All,

I have installed MS SQL 2005 in my system. I have created one database in my server. When iam running my application with this connections it is working fine in my local system. When i uploaded the database to some where dataserver, Iam not getting the connectins due to no remote access .

Some body suggested that to start the SQL browser in the SQL Server Configuration Manager.

I go there n tried to start the service when i put the service properties to Automatic or Manual i am getting the error message that is saying that the service is disabled cannot start automatically.

What could be the wrong?

Please suggest me.
Thanks and regards,

View 3 Replies View Related

Data Flow Source That Will Take A String Variable With Csv Structure?

Dec 21, 2006

I am able to use a custom script task to receive a MSMQ package and save the package contents to a flat file.

I can also use the bulk load task to push the flat file contents into a SQL table.

However, I would like to save the package contents to a variable (done, it works), and then pass that string variable to a data flow task for SQL upload. In other words, I don't see any reason to persist the msmq package contents to disk.

My question is: Which data flow source can I use that will accept a string variable? The string variable will then need to be processed with bulk load or an execute sql task.

Btw, the content of the string variable is a csv style string:

"01001","11/21/2006",15
"01001","11/21/2006",1
"01001","11/21/2006",25
"01001","11/21/2006",3


Thanks,

Trey

View 3 Replies View Related

Auhtentification Problem With Remote Data Access

Nov 9, 2005

Hello

View 1 Replies View Related

Remote Data Access For Server Compact 4.0

Apr 19, 2015

I am making a WPF app, and I want to use SQL Server Compact 4.0 for database management, but it doesn't support Remote data access, on the other hand SQL Server Compact 3.5 does support it but I can't use it in WPF ( I've installed it but can't add it in my project, it's not there in the templates). So is there any alternatives, for a light DBMS, that are not installed separately?

View 3 Replies View Related

How To Retrieve Data As XML From SQL Database

Feb 28, 2007

Hi,
I have a website which is designed to search for employee information. I have the search system working which does exactly what I want to, but as an added feature I want there to be a button which, when someone clicks on it, it takes whatever the previous search was and generates a set of data in XML format which is based on the results. For example:
User searches for all entries with Forename = John; Results are listed in a gridview as per expected.
User then presses button with XML on it, and page pops up with just the XML output on it, i.e. whatever results are on the gridview but in a nested XML format
<records>    <record>       <Forename>John</Forename>       <Surname>Smith</Surname>       <Email>j.smith@blah.com</Email>       <Ext>1234</Ext>       <DeptList>History</DeptList>    </record></records>
I have created a stored procedure which will take the parameters from the search boxes and return the above information, but I don't know if this is the best way. Here it is for those interested:
CREATE PROCEDURE ps_record_SELECT_NameSurnameEmailExtDeptasXML
@Forename varchar(50),
@Surname varchar(50),
@Email varchar(50),
@Ext varchar(4),
@DeptList varchar(50)
AS
SELECT Forename, Surname, Email, Ext, DeptList
FROM dbo.record
WHERE Forename LIKE COALESCE(@Forename,Forename) AND
Surname LIKE COALESCE(@Surname,Surname) AND
Email LIKE COALESCE(@Email,Email) AND
Ext LIKE COALESCE(@Ext,Ext) AND
DeptList LIKE COALESCE(@DeptList,DeptList)
FOR XML AUTO, ELEMENTS
If someone could be kind enough to help me out with this, I'd be really grateful.
Many thanks,
Tom

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved