I'm trying to Insert data from a linked server connection into one of my tables in the sql database. it seems to be giving me an error saying column cant be found. It only does this when I put the Where clause in the statement. I dont have the server in front of me but this is how my statement looks.
Insert into WorkList (DSK)
Select *
From OPENQUERY (SCH, 'Select Desk_ID from public.ACCOUNT Where Desk_ID = LA1')
I'm trying to Insert data from a linked server connection into one of my tables in the sql database. it seems to be giving me an error saying column cant be found. It only does this when I put the Where clause in the statement. I dont have the server in front of me but this is how my statement looks.
Insert into WorkList (DSK)
Select *
From OPENQUERY (SCH, 'Select Desk_ID from public.ACCOUNT Where Desk_ID = LA1')
The error that I get is the LA1 column cant be found? any help thanks
Import Hadoop Data into Analysis Services Tabular | Mentioned in the article is an example of using openquery to perform select statement on a Hive table through a linkedserver.
I was wondering how can i also perform an insert statement with openquery?
I understand that for example to insert into Hive is to use a Load command, like:
Code:
load data inpath '/tmp/data.csv' overwrite into table tableA;
How do i execute this with openquery? I've tried the example below and some slight variations but all I get in return were syntax errors
Code:
insert openquery (linkedserverName, 'load data inpath '/tmp/data.csv' overwrite into table tableA;')
how can insert statement script of data present in sql server can
be generated in a .sql file
Hi there can anyone help me to create a SQL Insert Statement. I dont know SQL at all.
To explain, I have two web pages. search.asp and results.asp.
search.asp has the following.
Form Name: searchForm
Set to: Post
Action: results.asp
Text Field: Keyword
Drop Down: CategoryTable
Drop Down: Location
UserName Session Variable: MM_UserName
results.asp has the following SQL which pulls all the results.
SELECT SupplierName, Location, ShortDescription, TimberSpecies, CategoryTitle, Country, CustomerType
FROM Query1
WHERE TimberSpecies LIKE '%MMColParam%' AND CategoryTitle LIKE '%MMColParam2%' AND Location LIKE '%MMColParam3%' AND CustomerType = 'Trade/Retail'
ORDER BY CategoryTitle, TimberSpecies ASC
The database & form I want to insert into.
tblSearcheResults
idSearch (AutoNumber)
location (Text) "Want to insert the 'Location' result here"
category (Text) "Want to insert the 'CategoryTable' result here"
user (Text) "Want to insert the UserName Session Variable result here"
result (Text) "Want to insert the 'Keyword' result here"
Please ask if u need more info.
Mally
Hi,
When a record is to be inserted, how can I check whether the insert is succeeded or not?
CREATE TRIGGER MY_TRIGGER ON MyTable AFTER INSERT
AS
DECLARE @id INT
DECLARE @name NVARCHAR(50)
SELECT @id=id,@name=name FROM INSERTED
BEGIN
INSERT INTO MySubTable(id,name) VALUES(@id,@name)
-- if failed, rollback
END
GO
GO
I am currently using openquery to insert data into a SQL 2000 database from a Lotus Notes database. The Lotus database is a linked server with a datasource named CLE_CARS_SF. My SQL table is called Webcases.
The query below works well because the table's columns are even in both databases:
Insert into Webcases select * from openquery(CLE_CARS_SF,
'Select * from Web_Cases')
I am moving this over to SQL 2005. The query works well, but I want to add a column to the Webcases SQL database and manually insert a value along with the openquery values.
My insert statement above no longer works because the column numbers don't match.
In a nutshell I would like a way to combine the following queries:
Insert into Webcases select * from openquery(CLE_CARS_SF,
'Select * from Web_Cases')
Insert into Webcases (insurancetype) Values ('SF')
--insurancetype is the new column.
Does anybody know how to pass variables to openquery statement? I executed the following statement against DB2 mainframe
and got an error message.
Thanks in advance
Hung-Ban
declare @deptname varchar(20)
select @deptname = 'HEAD OFFICE'
select DEPTNUMB,DEPTNAME from openquery(m1db2u,"select DEPTNUMB,DEPTNAME from Q.ORG
where DEPTNAME=@deptname")
Server: Msg 7399, Level 16, State 1, Line 4
OLE DB provider 'MSDASQL' reported an error.
[OLE/DB provider returned message: [IBM][CLI Driver][DB2] SQL0206N "@DEPTNAME " is not a column in an inserted table, updated table, or any table identified in a FROM clause or is not a valid transition variable for the subject table of a trigger. SQLSTATE=42703
]
I'm trying to use linked server to import big amount of data(around 9 million rows) from mysql to a sql 2005 table.
I plan to create a stored procedure and use job agent to run it at off-peak time everyday. The SP is like:
.....
Truncate table sqltblA;
Insert Into sqltblA(col1, col2, col3)
Select col1, col2, col3
From OpenQuery('mysql', 'Select col1, col2, col3 From mytblB')
.....
But when I tried to CREATE the above SP from management studio, seems the sql server was trying to get all data from table mytblB and ended up with failure after several minutes hang. The error message is sth. like "running out memeory". BTW, during that several minutes hang, I can't query mytblB using mysql's tool as well. it seems to me that table got locked.
However if i try to change the returned result to 100,000 rows by changing script to
Insert Into sqltblA(col1, col2, col3)
Select col1, col2, col3
From OpenQuery('mysql', 'Select col1, col2, col3 From mytblB Limit 100000')
The SP could be created without any problem and running the SP could get the data in table sqltblA.
But that's not what I want, I need all rows instead of a certain amount of rows.
Does anyone know what's going on there when I try to CREATE that SP and any solution to it?
Plus I tried to use SSIS as well. When I'm configuring the DataReader source, which uses ADO.NET's ODBC option to connect to mysql, if its sqlcommand is "select * from mytblB" without the Limit key word, then the configuration page will hang there as well and table mytblB is not accessible by using mysql tool. I'm just sick of wasting time on that and that's why I chose to use SP istead of SSIS. But same result. :-(
Hi Guys,
We already have work around when it comes to read xml data type from remote servers
remote server (eurodata3) table
create table temp_asaf
(
xmlCol XML
)
SELECT
Cast(a.XML_Data as XML) as XML_Data
FROM
OPENQUERY(eurodata3,'
SELECT TOP 10
Cast(xmlCol as Varchar(MAX)) as XML_Data
FROM
em_port.dbo.temp_asaf'
) a
Here is a question for you. How do I insert data into remote server when data type for a column is xml?
INSERT OPENQUERY (eurodata3, '
SELECT Cast(xmlCol as Varchar(MAX)) AS xmlCol
FROM em_port.dbo.temp_asaf')
VALUES ('<html><body>MS Sql Server</body></html>');
Running above code would give me the following error:
OLE DB provider "SQLNCLI" for linked server "eurodata3" returned message "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".
Msg 7344, Level 16, State 1, Line 1
The OLE DB provider "SQLNCLI" for linked server "eurodata3" could not INSERT INTO table "[SQLNCLI]" because of column "xmlCol". The user did not have permission to write to the column.
Location: memilb.cpp:1493
Expression: (*ppilb)->m_cRef == 0
SPID: 59
Process ID: 1660
Don't be misled by permission statement. I could successfully run similar query as long as the column is not xml on remote server. I would appreciate some ideas to get around with it.
One of our Oracle Tables changed and I am wondering if there's any way that I can query all of our Stored Procedures to try and find out if that Oracle Table Name is referenced in any of our SQL Server Stored Procedures OPENQUERY statements?
View 2 Replies View RelatedI was trying to figure out what the OPENQUERY Syntax is to Insert into SQL Server Table from Oracle Linked Server.
View 7 Replies View RelatedI've have a need with SQL Server 2005 (so I've no MERGE statement), I have to merge 2 tables, the target table has 10 fields, the first 4 are the clustered index and primary key, the source table has the same fields and index.Since I can't use the MERGE statement (I'm in SQL 2005) I have to make a double step operation, and INSERT and an UPDATE, I can't figure how to design the WHERE condition for the insert statement.
View 2 Replies View RelatedAn insert statement was not inserting all the data into a table. Found it very strange as the other fields in the row were inserted. I ran SQL profiler and found that sql statement had all the fields in the insert statement but some of the fields were not inserted. Below is the sql statement which is created dyanmically by a asp.net C# class. The columns which are not inserted are 'totaltax' and 'totalamount' ...while the 'shipto_name' etc...were inserted.there were not errors thrown. The sql from the code cannot be shown here as it is dynamically built referencing C# class files.It works fine on another test database which uses the same dlls. The only difference i found was the difference in date formats..@totalamount=1625.62,@totaltax=125.62are not inserted into the database.Below is the statement copied from SQL profiler.exec sp_executesql N'INSERT INTO salesorder(billto_city, billto_country, billto_line1, billto_line2, billto_name,billto_postalcode, billto_stateorprovince, billto_telephone, contactid, CreatedOn, customerid, customeridtype,DeletionStateCode, discountamount, discountpercentage, ModifiedOn, name, ordernumber,pricelevelid, salesorderId, shipto_city, shipto_country,shipto_line1, shipto_line2, shipto_name, shipto_postalcode, shipto_stateorprovince,shipto_telephone, StateCode, submitdate, totalamount,totallineitemamount, totaltax ) VALUES(@billto_city, @billto_country, @billto_line1, @billto_line2,@billto_name, @billto_postalcode, @billto_stateorprovince, @billto_telephone, @contactid, @CreatedOn, @customerid,@customeridtype, @DeletionStateCode, @discountamount,@discountpercentage, @ModifiedOn, @name, @ordernumber, @pricelevelid, @salesorderId,@shipto_city, @shipto_country, @shipto_line1, @shipto_line2,@shipto_name, @shipto_postalcode, @shipto_stateorprovince, @shipto_telephone,@StateCode, @submitdate, @totalamount, @totallineitemamount, @totaltax)',N'@billto_city nvarchar(8),@billto_country nvarchar(13),@billto_line1 nvarchar(3),@billto_line2 nvarchar(4),@billto_name nvarchar(15),@billto_postalcode nvarchar(5),@billto_stateorprovince nvarchar(8),@billto_telephone nvarchar(3),@contactid uniqueidentifier,@CreatedOn datetime,@customerid uniqueidentifier,@customeridtype int,@DeletionStateCode int,@discountamount decimal(1,0),@discountpercentage decimal(1,0),@ModifiedOn datetime,@name nvarchar(33),@ordernumber nvarchar(18),@pricelevelid uniqueidentifier,@salesorderId uniqueidentifier,@shipto_city nvarchar(8),@shipto_country nvarchar(13),@shipto_line1 nvarchar(3),@shipto_line2 nvarchar(4),@shipto_name nvarchar(15),@shipto_postalcode nvarchar(5),@shipto_stateorprovince nvarchar(8),@shipto_telephone nvarchar(3),@StateCode int,@submitdate datetime,@totalamount decimal(6,2),@totallineitemamount decimal(6,2),@totaltax decimal(5,2)',@billto_city=N'New York',@billto_country=N'United States',@billto_line1=N'454',@billto_line2=N'Road',@billto_name=N'Hillary Clinton',@billto_postalcode=N'10001',@billto_stateorprovince=N'New York',@billto_telephone=N'124',@contactid='8DAFE298-3A25-42EE-B208-0B79DE653B61',@CreatedOn=''2008-04-18 13:37:12:013'',@customerid='8DAFE298-3A25-42EE-B208-0B79DE653B61',@customeridtype=2,@DeletionStateCode=0,@discountamount=0,@discountpercentage=0,@ModifiedOn=''2008-04-18 13:37:12:013'',@name=N'E-Commerce Order (Before billing)',@ordernumber=N'BRKV-CC-OKRW5764YS',@pricelevelid='B74DB28B-AA8F-DC11-B289-000423B63B71',@salesorderId='9CD0E11A-5A6D-4584-BC3E-4292EBA6ED24',@shipto_city=N'New York',@shipto_country=N'United States',@shipto_line1=N'454',@shipto_line2=N'Road',@shipto_name=N'Hillary Clinton',@shipto_postalcode=N'10001',@shipto_stateorprovince=N'New York',@shipto_telephone=N'124',@StateCode=0,@submitdate=''2008-04-18 14:37:10:140'',@totalamount=1625.62,@totallineitemamount=1500.00,@totaltax=125.62
thanks
Hello
I have a problem with setting relations properly when inserting data using adonet. Already have searched for a solutions, still not finding a mistake...
Here's the sql management studio diagram :
and here goes the code1 DataSet ds = new DataSet();
2
3 SqlDataAdapter myCommand1 = new SqlDataAdapter("select * from SurveyTemplate", myConnection);
4 SqlCommandBuilder cb = new SqlCommandBuilder(myCommand1);
5 myCommand1.FillSchema(ds, SchemaType.Source);
6 DataTable pTable = ds.Tables["Table"];
7 pTable.TableName = "SurveyTemplate";
8 myCommand1.InsertCommand = cb.GetInsertCommand();
9 myCommand1.InsertCommand.Connection = myConnection;
10
11 SqlDataAdapter myCommand2 = new SqlDataAdapter("select * from Question", myConnection);
12 cb = new SqlCommandBuilder(myCommand2);
13 myCommand2.FillSchema(ds, SchemaType.Source);
14 pTable = ds.Tables["Table"];
15 pTable.TableName = "Question";
16 myCommand2.InsertCommand = cb.GetInsertCommand();
17 myCommand2.InsertCommand.Connection = myConnection;
18
19 SqlDataAdapter myCommand3 = new SqlDataAdapter("select * from Possible_Answer", myConnection);
20 cb = new SqlCommandBuilder(myCommand3);
21 myCommand3.FillSchema(ds, SchemaType.Source);
22 pTable = ds.Tables["Table"];
23 pTable.TableName = "Possible_Answer";
24 myCommand3.InsertCommand = cb.GetInsertCommand();
25 myCommand3.InsertCommand.Connection = myConnection;
26
27 ds.Relations.Add(new DataRelation("FK_Question_SurveyTemplate", ds.Tables["SurveyTemplate"].Columns["id"], ds.Tables["Question"].Columns["surveyTemplateID"]));
28 ds.Relations.Add(new DataRelation("FK_Possible_Answer_Question", ds.Tables["Question"].Columns["id"], ds.Tables["Possible_Answer"].Columns["questionID"]));
29
30 DataRow dr = ds.Tables["SurveyTemplate"].NewRow();
31 dr["name"] = o[0];
32 dr["description"] = o[1];
33 dr["active"] = 1;
34 ds.Tables["SurveyTemplate"].Rows.Add(dr);
35
36 DataRow dr1 = ds.Tables["Question"].NewRow();
37 dr1["questionIndex"] = 1;
38 dr1["questionContent"] = "q1";
39 dr1.SetParentRow(dr);
40 ds.Tables["Question"].Rows.Add(dr1);
41
42 DataRow dr2 = ds.Tables["Possible_Answer"].NewRow();
43 dr2["answerIndex"] = 1;
44 dr2["answerContent"] = "a11";
45 dr2.SetParentRow(dr1);
46 ds.Tables["Possible_Answer"].Rows.Add(dr2);
47
48 dr1 = ds.Tables["Question"].NewRow();
49 dr1["questionIndex"] = 2;
50 dr1["questionContent"] = "q2";
51 dr1.SetParentRow(dr);
52 ds.Tables["Question"].Rows.Add(dr1);
53
54 dr2 = ds.Tables["Possible_Answer"].NewRow();
55 dr2["answerIndex"] = 1;
56 dr2["answerContent"] = "a21";
57 dr2.SetParentRow(dr1);
58 ds.Tables["Possible_Answer"].Rows.Add(dr2);
59
60 dr2 = ds.Tables["Possible_Answer"].NewRow();
61 dr2["answerIndex"] = 2;
62 dr2["answerContent"] = "a22";
63 dr2.SetParentRow(dr1);
64 ds.Tables["Possible_Answer"].Rows.Add(dr2);
65
66 myCommand1.Update(ds,"SurveyTemplate");
67 myCommand2.Update(ds, "Question");
68 myCommand3.Update(ds, "Possible_Answer");
69 ds.AcceptChanges();
70
and that causes (at line 67):"The INSERT statement conflicted with the FOREIGN KEY constraint
"FK_Question_SurveyTemplate". The conflict occurred in database
"ankietyzacja", table "dbo.SurveyTemplate", column
'id'.
The statement has been terminated.
at System.Data.Common.DbDataAdapter.UpdatedRowStatusErrors(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)
at System.Data.Common.DbDataAdapter.UpdatedRowStatus(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)
at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping)
at System.Data.Common.DbDataAdapter.UpdateFromDataTable(DataTable dataTable, DataTableMapping tableMapping)
at System.Data.Common.DbDataAdapter.Update(DataSet dataSet, String srcTable)
at AnkietyzacjaWebService.Service1.createSurveyTemplate(Object[] o) in J:\PL\PAI\AnkietyzacjaWebService\AnkietyzacjaWebServicece\Service1.asmx.cs:line 397"
Could You please tell me what am I missing here ?
Thanks a lot.
This problem is being seen on SQL 2005 SP2 + cumulative update 4
I am currently successfully using the output clause of an insert statement to return the identity values for inserted rows into a table variable
I now need to add an "instead of insert" trigger to the table that is the subject of the insert.
As soon as I add the "instead of insert" trigger, the output clause on the insert statement does not return any data - although the insert completes successfully. As a result I am not able to obtain the identities of the inserted rows
Note that @@identity would return the correct value in the test repro below - but this is not a viable option as the table in question will be merge replicated and @@identity will return the identity value of a replication metadata table rather than the identity of the row inserted into my_table
Note also that in the test repro, the "instead of insert" trigger actually does nothing apart from the default insert, but the real world trigger has additional code.
To run the repro below - select each of the sections below in turn and execute them
1) Create the table
2) Create the trigger
3) Do the insert - note that table variable contains a row with column value zero - it should contain the @@identity value
4) Drop the trigger
5) Re-run the insert from 3) - note that table variable is now correctly populated with the @@identity value in the row
I need the behaviour to be correct when the trigger is present
Any thoughts would be much appreciated
aero1
/************************************************
1) - Create the table
************************************************/
CREATE TABLE [dbo].[my_table](
[my_table_id] [bigint] IDENTITY(1,1) NOT NULL,
[forename] [varchar](100) NULL,
[surname] [varchar](50) NULL,
CONSTRAINT [pk_my_table] PRIMARY KEY NONCLUSTERED
(
[my_table_id] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, FILLFACTOR = 70) ON [PRIMARY]
)
GO
/************************************************
2) - Create the trigger
************************************************/
CREATE TRIGGER [dbo].[trig_my_table__instead_insert] ON [dbo].[my_table]
INSTEAD OF INSERT
AS
BEGIN
INSERT INTO my_table
(
forename,
surname)
SELECT
forename,
surname
FROM inserted
END
/************************************************
3) - Do the insert
************************************************/
DECLARE @my_insert TABLE( my_table_id bigint )
declare @forename VARCHAR(100)
declare @surname VARCHAR(50)
set @forename = N'john'
set @surname = N'smith'
INSERT INTO my_table (
forename
, surname
)
OUTPUT inserted.my_table_id INTO @my_insert
VALUES( @forename
, @surname
)
select @@identity -- expect this value in @my_insert table
select * from @my_insert -- OK value without trigger - zero with trigger
/************************************************
4) - Drop the trigger
************************************************/
drop trigger [dbo].[trig_my_table__instead_insert]
go
/************************************************
5) - Re-run insert from 3)
************************************************/
-- @my_insert now contains row expected with identity of inserted row
-- i.e. OK
Hi All,
I've looked through the forum hoping I'm not the only one with this issue but alas, I have found nothing so I'm hoping someone out there will give me some assistance.
My problem is the case statement in my Insert Statement. My overall goal is to insert records from one table to another. But I need to be able to assign a specific value to the incoming data and thought the case statement would be the best way of doing it. I must be doing something wrong but I can't seem to see it.
Here is my code:
Insert into myTblA
(TblA_ID,
mycasefield =
case
when mycasefield = 1 then 99861
when mycasefield = 2 then 99862
when mycasefield = 3 then 99863
when mycasefield = 4 then 99864
when mycasefield = 5 then 99865
when mycasefield = 6 then 99866
when mycasefield = 7 then 99867
when mycasefield = 8 then 99868
when mycasefield = 9 then 99855
when mycasefield = 10 then 99839
end,
alt_min,
alt_max,
longitude,
latitude
(
Select MTB.LocationID
MTB.model_ID
MTB.elevation, --alt min
null, --alt max
MTB.longitude, --longitude
MTB.latitude --latitude
from MyTblB MTB
);
The error I'm getting is:
Incorrect syntax near '='.
I have tried various versions of the case statement based on examples I have found but nothing works.
I would greatly appreciate any assistance with this one. I've been smacking my head against the wall for awhile trying to find a solution.
In the below code i want to use select statement for getting customer
address1,customeraddress2,customerphone,customercity,customerstate,customercountry,customerfirstname,customerlastname
from customer table.Rest of the things will be as it is in the following code.How do i do this?
INSERT INTO EMImportListing ("
sql += " CustId,Title,Description,JobCity,JobState,JobPostalCode,JobCountry,URL,Requirements, "
sql += " IsDraft,IsFeatured,IsApproved,"
sql += " Email,OrgName,customerAddress1,customerAddress2,customerCity,customerState,customerPostalCode,
[code]....
Alright, so let me explain the details first.I have two tables. One is the default aspnet_users table that themembership class builds. that has GUID, username, lowereduser, and such.then I have another table called "UserSkills". That stores the GUID of the member, then the skills they have. so in that table i have. userID as GUID, then about 12 languages in 'bit' format.. (thats becuase in the webpage when they fill out there profile, all these are checkboxes. Basically all of the info is here http://www.listofcoders.com/profile.aspx?name=fenixsn. so there are a couple of bit fields, 1 text, and couple of varchars.anways, so i wanna build a powerful search thingy. where the users have the option to search a user that only does for ex say php, asp, asp.net. and is from location "Canada". ok so when they fill out the info, I want my SQL statement to do the following search the userskills table for the required fields. there might be more then 1 person that has the same profile, but different GUID. and then maybe using "Join" or another sql statement, grab there username, and last activity date from the users table that memberhship createes. so in short, how do i make a dynamic sql statement.
View 4 Replies View RelatedHello all - newbie post forthcoming....
I have a page that is writing to a database (Access) and I am having problems getting the actual SQL statement to execute properly. The code in question is as follows:
sql = "INSERT INTO article (maincat, subcatid, subject, article)"
sql = sql & " Values ('"
sql = sql & quotes(Request.form("maincat")) & "', '"
sql = sql & quotes(Request.form("subcatid")) & "', '"
sql = sql & quotes(Request.form("subject")) & "', '"
sql = sql & quotes(Request.form("article")) & "')"
The response I get is.... "Data Type Mismatch in Criteria Expression"
The post data does not have quotes around the data - I am thinking this is the problem.
Any help would be appreciated!
I am having trouble getting the SQL statement to return stats from a survey the way I want them. The table is set up as:
ID Q1 Q2 Q3 Q4
Responses for each question (Columns Q1 – Q4) will be a numerical value between 1-5. I want to count how many 1s, 2s, 3s, etc. I have tried different joins, self joins, unions and sub selections but cannot get the correct output.
I would like to get the output for each question as a single record, and if possible have a final column with an average for the question. But I can do that in the data binding if needed. Qs Ones Twos Threes Fours Fives
Q1 #of 1s #of 2s #of 3s #of 4s #of 5s
Q2 #of 1s #of 2s #of 3s #of 4s #of 5s
Q3 #of 1s #of 2s #of 3s #of 4s #of 5s
Any tips or SQL sample statements would be greatly appreciated.
Dear folks,
create table temptable(eno, ename) as select eno, ename from emp.
here the problem is it is asking for the datatype for the temporary table.
is it not possible to create the temp table without providing the datatypes?
thank you very much.
Vinod
Hello,I am attempting to build a MS SQL query that will return data from"today"; today being current day 8:00AM-10:00PM today. My goal is toreturn the data from a table that is written to throughout the day, thequery will provide the current grade of service in our call center.I am having difficulty defining my where clause:- I can accomplish my goal my statically defining my 'date between' asthe actual date and time (not ideal)- I can accomplish the second part of my date using CURRENT_TIMESTAMP;but I am unable to define the starting pointHere is where I am thus far:/* We are going to count the total calls into each queue from start ofbusiness today (8:00AM) to now */select COUNT(Result) as "Total Sales Calls Offered" fromdbo.QueueEncounterwhere Direction='0'andQueueID='1631'and/* This is where I get lost */Time between DATEPART(day, GETDATE()) and DATEPART(day, GETDATE())Clearly the last line returns zero as there are no calls between thesame date range. How can I add to that line, or write this to work?Any thoughts?Thanks for the help.-Chris
View 4 Replies View RelatedHi,I have a need to create a table detailing the ID of all contacts and thelast time they were contacted. This information is stored in 2 tables,'contact' and 'activity' (ID in the 'contact' table links to 'main_contact'in the 'activity' table).I guess I need some sort if iteration to go through each contact and findfind the last activity that took place against each of them (there many bemore than 1 activity against each contact) and then place the output valuesinto the new table.Can anyone show me how to go about this?Thanks!
View 2 Replies View RelatedThis may be a basic question, but defining anything other than a cursoris preffered.I have, as an example, 2 tables. One with customer data (addresses,phones, etc), the other is a listing of all 50 states (a cross referencefor short state alias to long state name, i.e. FL - Florida, etc...).I want to sort the out put by state long name, and show each customer inthe state ... BUT ...the output needs to be like so:FloridaABC,Inc Address1 City, State Zip, other InfoDummy Corp Address1 City, State Zip, other Info...GeorgiaXYZ, Inc Address1 City, State Zip, other Info...etc ...This is a basic heirarchical listing. Can this be done with a singleT-SQL statement or are cursors needed?Thanks in advance."Excellence is achieved through 1% inspiration and 99% perspiration." A.Einstein*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 1 Replies View Related
I'm trying to do this:
CREATE PROCEDURE [dbo].[spStoryDisplay]
-- Add the parameters for the stored procedure here
@clsYear int
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT *, "Class Year" =
CASE
WHEN year1 >= 1940 AND year1 < 1950 THEN '1940s'
WHEN year1 >= 1950 AND year1 < 1960 THEN '1950s'
WHEN year1 >= 1960 AND year1 < 1970 THEN '1960s'
WHEN year1 >= 1970 AND year1 < 1980 THEN '1970s'
WHEN year1 >= 1980 AND year1 < 1990 THEN '1980s'
WHEN year1 >= 1990 AND year1 < 2000 THEN '1990s'
WHEN year1 >= 2000 AND year1 < 2010 THEN '2000s'
END
FROM dvlStory
WHERE "Class Year" = @clsYear
ORDER BY lstName ASC
END
I kept getting this error:
Msg 207, Level 16, State 1, Procedure spStoryDisplay, Line 27
Invalid column name 'Class Year'.
So, my question is, how do I compare to the additional column "Class Year" to the parameter?
Hi, I'm having some difficulty creating the SQL Statement for getting some data from a table:
I have the following table of data
__User___Votes___Month
__A_______14______2__A_______12______3__A_______17______4__A_______11______5
__B_______19______2__B_______12______3__B_______15______4
I want to beable to pull out the total number of votes a user has had over a period of months.
eg Total up each users users votes for months 4 and 5
that would give:
__User____TotalVotes
___A________28___B________15
An added complecation is that user B does not have any data for month 5
Any help or pointers would be fanstatic
Many thanks
I maintain a simple employment (job) tracking web application.
Jobs can be set to 5 different statuses: Open, Closed, Filled, Pending, or Cancelled.
There is a table in the database called statusLog, which records everytime a job is set to opened, or set to closed, etc. It records the job number, the date it was changed, and what the job was changed to.
Here is a short example of what a few entries might look like:
Status Date Job number
Open10/6/2002 2:34:56 PMTEST2845
Hold10/11/2002 12:19:29 PMTEST2845
Closed10/29/2002 2:00:54 PMTEST2845
Pending10/25/2002 3:37:06 PMTEST2877
What I need to do is write an SQL query that will return to me all entries in this table, between two certain dates, that ONLY have entries during those dates.
Basically I need to know how many "new" jobs were set to open during a month. I can easily just do a count of how many jobs were set to open, but this will not give a count of "new" jobs.
Example: during june a job could be set to open. Then in july it could be set to pending. Then in august, it could be re-opened, and set to open.
If I ran this query for the month of August, it would return that job as being opened in august. But it wasn't a new job, meaning it had already been in the system in previous months.
Is there some way I can select all "Open" jobs, between a certain date, that do not exist anywhere in the table previous to the date it was entered? This would give me a result set containing only new jobs.
The only way I've thought of yet is to get a result set of all jobs simply set to open during a month, then one by one for each record, go back and run another SQL query to see if it exists in the table anywhere other than in that month.
This seems horribly inefficient to me however, as I do not want to be doing 34,000 independent SQL calls for every single "open" job it finds during a certain month.
thanks
I am attempting to update a table that drives a report. The report is at the order level. An order can have several types of demand (revenue $): Ship and/or Backordered. When I update one of these demand fields on the report table, I would expect those orders that have that type of demand to update, and those orders that don't have that type of demand to remain at their current value ($0 in this example which is the table default). But what is happening is that orders that don't have that type of demand are changing to NULL. What am i doing wrong with my update statement that is causing this?
Code:
CREATE TABLE #sales(
OrdNo int,
StatusGrp varchar(10),
Demand decimal(10,2)
)
INSERT INTO #sales
VALUES (1,'Ship',100.00)
[Code] .....
I have four total tables.Table One (Documents)- List of Documents. Each record has two fieldsrelated to this issue. First field (Document_ID) is the ID of thedocument, second field is the ID of the record (Task_ID) it isassociated to in Table Two.Table Two (Activities)- List of activities. Each record has two fieldsrelated to this issue. First field (Activity_ID) is the ID of theactivity, the second field (Group_ID) is the ID of the record it isassociated to in Table Three.Table Three (Groups) - List of groups. Each record has two fieldsrelated to this issue. First field (Group_ID) is the ID of the group,the second field (Stage_ID) is the ID of the record it is associated toin Table four.Table Four (Stages)- List of Event Stages. Each record has two fieldsthat is related to this issue. The first field (Stage_ID) is the ID ofthe stage of an event, the second record is the ID number associated tothe event. This last ID is a known value.20000024 = the Event IDI'm trying to come up with a list of Documents from the first tablethat is associated to an Event in the Fourth table.Query Analyzer shows no errors within the script. It just doesn'treturn any data. I know that it should, if it does what I'm wanting itto do.SELECT Document_ID FROM Documents as A where ((SELECT Event_ID FROMStages as D WHERE (D.Stage_ID = (SELECT Stage_ID FROM Groups as C WHERE(C.Group_ID = (SELECT Group_ID FROM Activity as B WHERE (B.Activity_ID= A.Activity_ID))))))= '20000024')
View 2 Replies View RelatedI'm quite stuck with this:I have an import table called ReferenceMatchingImport which containsdata that has been sucked from a data submission. The contents ofthis table have to be imported into another table ExternalReferencewhich has various foreign keys.This is simple but one of these keys says that the value inExternalReference.CompanyRef must be in the CompanyReference table.Of course if this is an initial import then it will not be so as partof my script I must insert a new row into CompanyReference andpopulate ExternalReference.CompanyRef with the identity column of thistable.I thought a good idea would be to use an SP which inserts a new rowand returns @@Identity as the value to insert. However this doesn'twork as far as I can tell. Is there a approved way to perform thissort of opperation? My code is below.Thanks.ALTER PROCEDURE SP00ReferenceMatchingImportAS/*Just some integrity checking going on here*/INSERT ExternalReference(ExternalSourceRef,AssetGroupRef,CompanyUnitRef,EntityTypeCode,CompanyRef, --this is the unknown ref which is returned by the spExternalReferenceTypeCode,ExternalReferenceCompanyReferenceMapTypeCode,StartDate,EndDate,LastUpdateBy,LastUpdateDate)SELECT rmi.ExternalDataSourcePropertyRef,rmi.AssetGroup,rmi.CompanyUnit,rmi.EntityType,SP01InsertIPDReference rmi.EntityType, --here I'm trying to run thesp so that I can use the return value as the insert value1,1,GETDATE(),GETDATE(),'RefMatch',GETDATE()FROM ReferenceMatchingImport rmiWHERE rmi.ExternalDataSourcePropertyRef NOT IN (SELECT ExternalSourceRefFROM ExternalReference)
View 3 Replies View Relatedi am trying to create a trigger which when I insert a "y" on a student table in insCheck column, instructor table will create a record of the same person.
The reason is there are two tables are in my DB, student and instructor table. Student can be a instructor so whenever insCheck conlum in studnent table is checked as "y", instructor table should be populated.
Both table studentId and instructorId inserted as manually so that I am not sure how i should populate the instructor's table just fName, mI, and lName and I have go back to the instructor table and populate the rest of the table manually or is there any way to poppulate the insturctorid also while trigger is populate the name onthe instructor table.
My Two tables DDL are like down below
create table student(
studentId char(4) not null primary key,
fName varchar(25) not null,
mI char(1) null,
lName varchar(25) not null,
insCheck char(1) not null,
);
create table instructor(
instructorId char(4) not null primary key,
fName varchar(25) not null,
mI char(1) null,
lName varchar(25) not null,
instructorQual varchar(100) not null,
);
thanks for your help in advance!
gazawaymy
i created a simple table to record all uploaded files to my website. now, it works, but the problem is, it posts to the table 2 times, as in it executes "Button1_Click" event twice. The result is i get two records which are the same, and only differs in primary key (because i set it as an autonumber). how do i fix this? thanks in advance
here's the code:
HTML: <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConflictDetection="CompareAllValues"
ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
InsertCommand="INSERT INTO [Base_Files] ([User_ID], [Date_Posted], [File_Type], [File_Size], [File_Name], [File_Description]) VALUES (@User_ID, @Date_Posted, @File_Type, @File_Size, @File_Name, @File_Description)">
<InsertParameters>
<asp:Parameter Name="User_ID" />
<asp:Parameter Name="Date_Posted" />
<asp:Parameter Name="File_Type" />
<asp:Parameter Name="File_Size" />
<asp:Parameter Name="File_Name" />
<asp:Parameter Name="File_Description" />
</InsertParameters>
</asp:SqlDataSource>
VB:Sub Button1_Click(ByVal sender As Object, ByVal e As EventArgs) Handles Button1.Click
If FileUpLoad1.HasFile Then FileUpLoad1.SaveAs(Server.MapPath(".") & "files" & FileUpLoad1.FileName)
Label1.Text = "Received <strong>" & FileUpLoad1.FileName & "</strong> Content Type: " & FileUpLoad1.PostedFile.ContentType & ", Length: " & FileUpLoad1.PostedFile.ContentLength & "bytes, at " & Date.Now.ToString("MMM dd, yyyy, h:mmtt")
SqlDataSource1.InsertParameters("File_Name").DefaultValue = FileUpLoad1.FileName.ToString()
SqlDataSource1.InsertParameters("User_ID").DefaultValue = User.Identity.Name.ToString()
SqlDataSource1.InsertParameters("File_Type").DefaultValue = FileUpLoad1.PostedFile.ContentType
SqlDataSource1.InsertParameters("File_Size").DefaultValue = FileUpLoad1.PostedFile.ContentLength
SqlDataSource1.InsertParameters("File_Description").DefaultValue = txtDescription.Text.ToString()
SqlDataSource1.InsertParameters("Date_Posted").DefaultValue = Date.Now.ToString("MMM dd, yyyy, h:mmtt")
SqlDataSource1.Insert()
Else
Label1.Text = "No uploaded file"
End If
End Sub