|
I need to grab data from a table not in my database.
The other database is on the Ms SQL server.
Now I know how to access it, but I was just thinking what would be the best method for accessing the data.
First I was thinks about just working with the tables in the other database and using sored procedures to insert and access the data.
The second option is to make views to the other database and use them as if there locale tables.
The second option makes it easy to make any change later, but this probably not happen and I could just act a an overhead.
Anyone a pointers on the best way to do this?
|
|
|
|
|
How can i convert a getdate() result to a style of yyyymmddhhmmss
Thanks
Javierarka
|
|
|
|
|
select replace(convert(varchar(10), GetDate(), 112) + convert(varchar(8), GetDate(), 114), ':', '')
Regards
Andy
|
|
|
|
|
select replace(replace(replace(convert(varchar(19),getdate(),120),'-',''),':',''),' ','')
|
|
|
|
|
Ok, I have a concurrency problem (I think) while trying to create a field using CDaoDatabase & CDaoRecordset.
Long and short of it is that I need to create a field in an access database while still keeping a recordset that is connected to the table open. When I create the field using the following code:
CDaoTableDef tableDef(&m_daoDB);<br />
tableDef.Open(sTableName);<br />
tableDef.CreateField(newField);
I also have a recordset open on the same table. The last line throws an exception with a message: The database engine could not lock table 'Test Datatypes' because it is already in use by another person or process.
Any help or thoughts on that would be wonderful. Thanks in advance.
|
|
|
|
|
Is there a way to increment a field that is an INT value, without having to query the existing value and then add one and write it back?
For example, if I have a table like this:
Column Name | Datatype
======================
ID | INT (Identity)
Count | INT Then is there a way I can do something like this?
INCREMENT Count FROM MyTable WHERE ID='1' I'm using Microsoft SQL Server 2005. Thanks in advance for any help!
|
|
|
|
|
UPDATE MyTable SET Count=Count+1 WHERE ID='1'
|
|
|
|
|
  Thanks!!!
|
|
|
|
|
Hi,
This is quick question regarding calling function from join.
In my project i am facing one situation where i need to use join
between one temporary table and database table.From that join statement
i need to call one function which taking 4 input parameters of temporary table and 4 input parameters of database table and returns one varchar2 value.
Now here is question
Let's say i creates one join statement like :
"Select temp_table.firstname,temp_table.lastname,temp_table.ssn,
db_table.firstname,db_table.lastname,db_table.ssn,
getmatchtype(temp_table.firstname,.. ,..,..,..,db_table.firstname,..,..,..)
from temporary_table ,database_table
where
(
/*
some code in where clause which joins temporary table and database table.
*/
)
Now my question is that do my
getmatchtype(temp_table.firstname,.. ,..,..,..,db_table.firstname,..,..,..)
function returns same value for value based on input parameter or it returns
different differnt value every time.
Or can some one say me that how my function works in join query???
Actually , my requirement is that depends on where clause of join,
function takes input parameters and return back to select query and
everytime function should return new value for one by one record.
Is this join works of not for that??
Can some body help me out for this??
Thankx.
regards,
montu3377
|
|
|
|
|
My first idea would be to try it and see, but I think i see what you mean and your function should produce a different value based on the row, as long as the input parameters are different, of course...
|
|
|
|
|
I have the Stored Procedure below which I run using the code below that but each time I run the procedure the row gets added to the table but the @Identity parameter is always the value 1. There is a column on the Invoice table called InvoiceID which has Identity set as Yes.
Am I doing something wrong?
Thanks
Paul
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <author,,name>
-- Create date: <create date,,="">
-- Description: <description,,>
-- =============================================
ALTER PROCEDURE [dbo].[InsertInvoice]
-- Add the parameters for the stored procedure here
@IClientID int
,@InvoiceNumber varchar(15)
,@InvoiceDate datetime
,@PaymentStatus tinyint
,@Description varchar(255)
,@InvoiceNotes text
,@ICustomCheck1 bit
,@ICustomCheck2 bit
,@ICustomCheck3 bit
,@ICustomCheck4 bit
,@ICustomCheck5 bit
,@ICustomCheck6 bit
,@IsAgent bit
,@Identity int OUTPUT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
INSERT INTO [BreedSociety].[dbo].[Invoice]
([IClientID]
,[InvoiceNumber]
,[InvoiceDate]
,[PaymentStatus]
,[Description]
,[InvoiceNotes]
,[ICustomCheck1]
,[ICustomCheck2]
,[ICustomCheck3]
,[ICustomCheck4]
,[ICustomCheck5]
,[ICustomCheck6]
,[IsAgent])
VALUES
(@IClientID
,@InvoiceNumber
,@InvoiceDate
,@PaymentStatus
,@Description
,@InvoiceNotes
,@ICustomCheck1
,@ICustomCheck2
,@ICustomCheck3
,@ICustomCheck4
,@ICustomCheck5
,@ICustomCheck6
,@IsAgent)
SET @Identity = SCOPE_IDENTITY()
END
SqlCommand cmd = dCon.CreateCommand();
cmd.CommandText = "InsertClient";
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.Add("@HoldingNumber", SqlDbType.VarChar).Value = client.HoldingNumber;
cmd.Parameters.Add("@TradingTitle", SqlDbType.VarChar).Value = client.TradingTitle;
cmd.Parameters.Add("@Surname", SqlDbType.VarChar).Value = client.Surname;
cmd.Parameters.Add("@ContactName", SqlDbType.VarChar).Value = client.ContactName;
cmd.Parameters.Add("@Address1", SqlDbType.VarChar).Value = client.Address1;
cmd.Parameters.Add("@Address2", SqlDbType.VarChar).Value = client.Address2;
cmd.Parameters.Add("@Address3", SqlDbType.VarChar).Value = client.Address3;
cmd.Parameters.Add("@Address4", SqlDbType.VarChar).Value = client.Address4;
cmd.Parameters.Add("@Postcode", SqlDbType.VarChar).Value = client.Postcode;
cmd.Parameters.Add("@Tel", SqlDbType.VarChar).Value = client.Tel;
cmd.Parameters.Add("@Tel2", SqlDbType.VarChar).Value = client.Tel2;
cmd.Parameters.Add("@Mobile", SqlDbType.VarChar).Value = client.Mobile;
cmd.Parameters.Add("@Fax", SqlDbType.VarChar).Value = client.Fax;
cmd.Parameters.Add("@Email", SqlDbType.VarChar).Value = client.Email;
cmd.Parameters.Add("@Agent", SqlDbType.TinyInt).Value = client.Agent;
cmd.Parameters.Add("@CTSUsername", SqlDbType.VarChar).Value = client.CTSUsername;
cmd.Parameters.Add("@CTSPassword", SqlDbType.VarChar).Value = client.CTSPassword;
cmd.Parameters.Add("@LMeNTRY", SqlDbType.Bit).Value = client.LMeNTRY;
cmd.Parameters.Add("@FarmMatters", SqlDbType.Bit).Value = client.FarmMatters;
cmd.Parameters.Add("@ClientNotes", SqlDbType.VarChar).Value = client.ClientNotes;
cmd.Parameters.Add("@Support", SqlDbType.TinyInt).Value = client.Support;
cmd.Parameters.Add("@Licenes", SqlDbType.Int).Value = client.Licenes;
cmd.Parameters.Add("@IsBureau", SqlDbType.Bit).Value = client.IsBureau;
cmd.Parameters.Add("@OperatingSystem", SqlDbType.TinyInt).Value = client.OperatingSystem;
cmd.Parameters.Add("@SBI", SqlDbType.VarChar).Value = client.SBI;
cmd.Parameters.Add("@CustomCheck1", SqlDbType.Bit).Value = client.CustomCheck1;
cmd.Parameters.Add("@CustomCheck2", SqlDbType.Bit).Value = client.CustomCheck2;
cmd.Parameters.Add("@CustomCheck3", SqlDbType.Bit).Value = client.CustomCheck3;
cmd.Parameters.Add("@CustomCheck4", SqlDbType.Bit).Value = client.CustomCheck4;
cmd.Parameters.Add("@CustomCheck5", SqlDbType.Bit).Value = client.CustomCheck5;
cmd.Parameters.Add("@CustomCheck6", SqlDbType.Bit).Value = client.CustomCheck6;
cmd.Parameters.Add("@Identity", SqlDbType.Int);
cmd.Parameters["@Identity"].Direction = System.Data.ParameterDirection.Output;
if(isClosed) dCon.Open();
cmd.ExecuteNonQuery();
if(isClosed) dCon.Close();
return Convert.ToInt32(cmd.Parameters["@Identity"].Value);
|
|
|
|
|
Stored procedure name as defined:
Howellsy wrote: ALTER PROCEDURE [dbo].[InsertInvoice]
Stored procedure called by your code:
Howellsy wrote: cmd.CommandText = "InsertClient";
Maybe that could be the problem? Or is it just a copy-past error (looks like it)?
It would be helpful to see the definition of the Invoice table.
Jon Sagara
I controlled my laughter and simple said "No,I am very busy,so I can't write any code for you". The moment they heard this all the smiling face turned into a sad looking face and one of them farted. So i had to leave the place as soon as possible.
--Mr.Prakash
Blog | Site | Articles
|
|
|
|
|
It was just a copy and paste error, that is the actually Stored Procedure, which is much the same but with different data fields. Below the Stored Procedure is the sql for creating the table. Not sure what the table definition is and how to get it, could you please tell me how to get it?
Kind Regards
Paul
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <author,,name>
-- Create date: <create date,,="">
-- Description: <description,,>
-- =============================================
ALTER PROCEDURE [dbo].[InsertClient]
-- Add the parameters for the stored procedure here
@HoldingNumber varchar(12)
,@TradingTitle varchar(30)
,@Surname varchar(30)
,@ContactName varchar(30)
,@Address1 varchar(30)
,@Address2 varchar(30)
,@Address3 varchar(30)
,@Address4 varchar(30)
,@Postcode varchar(30)
,@Tel varchar(20)
,@Tel2 varchar(20)
,@Mobile varchar(20)
,@Fax varchar(20)
,@Email varchar(255)
,@Agent tinyint
,@CTSUsername varchar(15)
,@CTSPassword varchar(15)
,@LMeNTRY bit
,@FarmMatters bit
,@ClientNotes varchar(255)
,@Support tinyint
,@Licenes int
,@IsBureau bit
,@OperatingSystem tinyint
,@SBI varchar(20)
,@CustomCheck1 bit
,@CustomCheck2 bit
,@CustomCheck3 bit
,@CustomCheck4 bit
,@CustomCheck5 bit
,@CustomCheck6 bit
,@Identity bit OUTPUT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
INSERT INTO [BreedSociety].[dbo].[Client]
([HoldingNumber]
,[TradingTitle]
,[Surname]
,[ContactName]
,[Address1]
,[Address2]
,[Address3]
,[Address4]
,[Postcode]
,[Tel]
,[Tel2]
,[Mobile]
,[Fax]
,[Email]
,[Agent]
,[CTSUsername]
,[CTSPassword]
,[LMeNTRY]
,[FarmMatters]
,[ClientNotes]
,[Support]
,[Licenes]
,[IsBureau]
,[OperatingSystem]
,[SBI]
,[CustomCheck1]
,[CustomCheck2]
,[CustomCheck3]
,[CustomCheck4]
,[CustomCheck5]
,[CustomCheck6])
VALUES
(@HoldingNumber
,@TradingTitle
,@Surname
,@ContactName
,@Address1
,@Address2
,@Address3
,@Address4
,@Postcode
,@Tel
,@Tel2
,@Mobile
,@Fax
,@Email
,@Agent
,@CTSUsername
,@CTSPassword
,@LMeNTRY
,@FarmMatters
,@ClientNotes
,@Support
,@Licenes
,@IsBureau
,@OperatingSystem
,@SBI
,@CustomCheck1
,@CustomCheck2
,@CustomCheck3
,@CustomCheck4
,@CustomCheck5
,@CustomCheck6)
SET @Identity = SCOPE_IDENTITY()
END
USE [BreedSociety]
GO
/****** Object: Table [dbo].[Client] Script Date: 01/26/2007 08:39:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Client](
[ClientID] [int] IDENTITY(1,1) NOT NULL,
[HoldingNumber] [varchar](12) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[TradingTitle] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Surname] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[ContactName] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Address1] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Address2] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Address3] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Address4] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Postcode] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Tel] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Tel2] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Mobile] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Fax] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Email] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Agent] [tinyint] NOT NULL,
[CTSUsername] [varchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[CTSPassword] [varchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[LMeNTRY] [bit] NOT NULL,
[FarmMatters] [bit] NOT NULL,
[ClientNotes] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Support] [tinyint] NOT NULL,
[Licenes] [int] NOT NULL,
[IsBureau] [bit] NOT NULL,
[OperatingSystem] [tinyint] NOT NULL,
[SBI] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[CustomCheck1] [bit] NOT NULL,
[CustomCheck2] [bit] NOT NULL,
[CustomCheck3] [bit] NOT NULL,
[CustomCheck4] [bit] NOT NULL,
[CustomCheck5] [bit] NOT NULL,
[CustomCheck6] [bit] NOT NULL,
CONSTRAINT [PK_Client] PRIMARY KEY CLUSTERED
(
[ClientID] ASC
) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
|
|
|
|
|
Howellsy wrote: ,@Identity bit OUTPUT
A BIT can only have a value of 1 or 0 (or NULL). Change the type to INT.
|
|
|
|
|
Thank you very much, what a silly mistake to make.
|
|
|
|
|
Hi
i want to select the values which are not in the other table mentioned below:
SELECT DISTINCT message_type,tran_type,new_account,rsp_code_rsp,fraudrspcode
FROM TRANSACTIONS WHERE
message_type AND tran_type AND new_account AND rsp_code_rsp AND fraudrspcode NOT IN
(SELECT message_type,tran_type,new_account,rsp_code_rsp,fraudrspcode
FROM Tran_Types_Dimension)
the following error occuring:
An expression of non-boolean type specified in a context where a condition is expected, near 'AND'.
plz help this.
SHAHZAD ASLAM
Software Engineer
shehzadaslam@hotmail.com
|
|
|
|
|
Try these steps........
First u make inner join with both table
and check all fields seperately with not equel operator ( add condition with And).
Or,
U can also use outer join. plz refer outer join...........
Parwej Back...............DON of Developer.......
Parwej Ahamad
g_parwez@rediffmail.com
|
|
|
|
|
The error you are getting here is because you have not defined boolean conditions for your AND statements. For instance, AND new_account, "AND new_account what"? Does this mean AND new_account = 1?
the last thing I want to see is some pasty-faced geek with skin so pale that it's almost translucent trying to bump parts with a partner - John Simmons / outlaw programmer
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
if i remove the boolean field new_account from query still same error is occuring.
|
|
|
|
|
AND new_account AND rsp_code_rsp
But what about rsp_code_rsp - That is exactly the same thing.
Also your IN(SELECT...) statement isn't right either. You should only be specifying a single column in the SELECT statement in the IN() expression.
|
|
|
|
|
rsp_code_rsp is not boolean field the only boolean field is new_account but if i remove this new_account from query the same error is occuring.
|
|
|
|
|
SELECT DISTINCT message_type,tran_type,new_account,rsp_code_rsp,fraudrspcode
FROM TRANSACTIONS WHERE
message_type AND tran_type AND new_account AND rsp_code_rsp AND fraudrspcode NOT IN
(SELECT message_type,tran_type,new_account,rsp_code_rsp,fraudrspcode
FROM Tran_Types_Dimension)
Right - let's analyze this statement:
SELECT DISTINCT message_type,tran_type,new_account,rsp_code_rsp,fraudrspcode
FROM TRANSACTIONS WHERE
message_type What does this equate to?
AND tran_type Same here...
AND new_account And here...
AND rsp_code_rsp And here...
AND fraudrspcode NOT IN
(SELECT message_type,tran_type,new_account,rsp_code_rsp,fraudrspcode
FROM Tran_Types_Dimension)
Change to reference only one attribute here.
It is obvious from the above that you need to supply the other side of the test in each case highlighted in bold. For instance, you may want to specify AND tran_type = 2.
However, I think what you are trying to do in your statement is to test to retrieve records from transactions where there is not a corresponding entry in tran_types_dimension. Is this correct?
the last thing I want to see is some pasty-faced geek with skin so pale that it's almost translucent trying to bump parts with a partner - John Simmons / outlaw programmer
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
In which case I would do this:
SELECT DISTINCT
t1.message_type, t1.tran_type, t1.new_account,
t1.rsp_code_rsp, t1.fraudrspcode
FROM TRANSACTIONS t1
LEFT OUTER JOIN Tran_Types_Dimension t2
ON t1.message_type = t2.message_type AND
t1.tran_type = t2.tran_type AND
t1.new_account = t2.new_account AND
t1.rsp_code_rsp = t2.rsp_code_rsp AND
t1.fraudrspcode = t2.fraudrspcode
WHERE
t2.message_type IS NULLThe LEFT JOIN causes the database to return all rows where there is a match (the boolean condition in the ON clause), plus any rows from the left-hand table in the join where there is no match in the right-hand table. In the latter case, the fields from the right-hand table are filled with NULLs. The WHERE clause causes all the rows that actually did match to be discarded, leaving only those where there wasn't a match (i.e. NOT IN). This only works properly if the field you're testing cannot (or, at least, does not) contain NULLs. Strictly you should probably test that all columns from the right-hand table are NULL, but it saves typing to only test one.
|
|
|
|
|
Thanks for help me i have made changes in the query now i m thinking that it is not effecient what u think?
SELECT DISTINCT message_type,tran_type,new_account,rsp_code_rsp,fraudrspcode
FROM TRANSACTIONS WHERE
new_account=1 AND
(message_type NOT IN
(SELECT message_type FROM Tran_Types_Dimension) OR
tran_type NOT IN
(SELECT tran_type FROM Tran_Types_Dimension) OR
rsp_code_rsp NOT IN
(SELECT rsp_code_rsp FROM Tran_Types_Dimension) OR
fraudrspcode NOT IN
(SELECT fraudrspcode FROM Tran_Types_Dimension)
) OR
new_account<>1 AND
(message_type NOT IN
(SELECT message_type FROM Tran_Types_Dimension) OR
tran_type NOT IN
(SELECT tran_type FROM Tran_Types_Dimension) OR
rsp_code_rsp NOT IN
(SELECT rsp_code_rsp FROM Tran_Types_Dimension) OR
fraudrspcode NOT IN
(SELECT fraudrspcode FROM Tran_Types_Dimension))
what type of indexes should i made on transaction table?
|
|
|
|
|
SQL requires that you actually compare a value to something in a comparison expression. It does not implicitly convert a value to a boolean as C++ does, or perform conditional logic based on integers as C does. This is true even for bit columns/variables.
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
