I have created a table with the following columns...
Date(datetime),Actual (Int),Planned (Int)
I need to insert weekending dates starting from 23/04/04 looping
thru'for the next 52weeks automatically into the date column.
Then in the actual and planned colums, I need to insert a count of
some records in the table.
I will appreciate help on a SQL query to achieve this!
I can't see what is going on, this is the situation:
I call the Pull method, specify the table to be affected, the query to be used, the connection string to the remote SQL server, the tracking options (On) and the Error table. The pull method executes with no errors however, no table is ever created. I don't know why, here's what I have done so far:
I read the SQL BOOKS ONLINE help on preparing RDA, I set up the IIS virtual directory for anonymous access and on the connection string I send in the user name and password for the SQL server, I went into the SQL Server and grated access to the user name to the database that I am going to access and I made the user a db_owner.
So, according to SQL BOOKS ONLINE I have everything right however, it won't populate, so right now I am open to suggestions on how to get this to work, heres the code:
------------------------------------------------------------------------------------------------------------------
string rdaOleDbConnectString = "Provider=SQLOLEDB;Data Source=<Server>;Initial Catalog=<DB>; User Id=<User>;Password=<Password>"; (it's not exactly like this, but in it has the proper values)
string connectionString = "Data Source="\Program Files\client\db\MobileDB.sdf"";
SqlCeRemoteDataAccess rda = new SqlCeRemoteDataAccess("http://10.1.1.206/mobile/sqlcesa30.dll",
connectionString);
IList _tableNames = new ArrayList();
IList _queries = new ArrayList();
############
Code that prepares tables and queries
############
for (int counter = 0; counter < _tableNames.Count; counter++)
{
rda.Pull(_tableNames[counter].ToString(), _queries[counter].ToString(), rdaOleDbConnectString, RdaTrackOption.TrackingOn, "MobileError");
}
the For loop runs with no problems but no data is ever put (or tables created) into the Mobile DB.
I have written following SQL query, this creates temporary table, inserts rows into it. I need to create VIEW "vw_NumberOfAttachments" in the database. I initially created table using "CREATE TABLE" but then i got error as VIEW can not be filled by temporary table. Hence I am using DECLARE TABLE
--------------------------------
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE sp_GetViewNumberOfAttachments
-- Add the parameters for the stored procedure here
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @emailMessageID bigint
DECLARE @metaDataStorageID bigint
DECLARE @numberOfAttachments int
DECLARE @AttachmentDetails TABLE
(
emailMessageID bigint,
metaDataStorageID bigint,
numberOfAttachments int
)
DECLARE ATTACHMENT_CURSOR CURSOR
FOR
SELECT emailMessageID, metaDataStorageID
FROM ppaEmailMessage
WHERE hasAttachments='true'
OPEN ATTACHMENT_CURSOR
FETCH NEXT FROM ATTACHMENT_CURSOR INTO @emailMessageID, @metaDataStorageID
WHILE @@FETCH_STATUS = 0
BEGIN
-- here the table name need to get dynamically the name of the attachment table
-- for a moment it is written as ppaMsOfficeDoc, but that should change dynamically
set @numberOfAttachments = (SELECT count(*) FROM ppaMsOfficeDoc WHERE metaDataStorageID = @metaDataStorageID)
INSERT INTO @AttachmentDetails(emailMessageID, metaDataStorageID, numberOfAttachments)
VALUES (@emailMessageID, @metaDataStorageID, @numberOfAttachments)
FETCH NEXT FROM ATTACHMENT_CURSOR INTO @emailMessageID, @metaDataStorageID
END
CLOSE ATTACHMENT_CURSOR
DEALLOCATE ATTACHMENT_CURSOR
IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_NAME = 'vw_NumberOfAttachments')
DROP VIEW vw_NumberOfAttachments
GO
CREATE VIEW vw_NumberOfAttachments
AS
SELECT @AttachmentDetails.emailMessageID, @AttachmentDetails.metaDataStorageID, @AttachmentDetails.numberOfAttachments
FROM @AttachmentDetails
GO
END
GO
----------------------
I am getting following errors:
-----------
Msg 102, Level 15, State 1, Procedure sp_GetViewNumberOfAttachments, Line 57
Incorrect syntax near 'vw_NumberOfAttachments'.
Msg 137, Level 15, State 2, Procedure vw_NumberOfAttachments, Line 3
Must declare the scalar variable "@AttachmentDetails".
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near 'END'.
-----------
Can anyone please suggest whats wrong in there? Many thanks
I have an Integration Services package that loads new data into tables that are dimension tables wi my cube. The same situation exists for my fact table. If I perform an "Analysis Services Processing Task" for the dimensions ,cube and measures, will that move the new data into my cube or do I need to perform the "Dimension Processing Destination" data flow task prior to this? Is the initial processing task good enough?
thx,
-Marilyn
i am using visual web developer 2005 and SQL Express 2005 with VB as the code behindi have a button and in the button click event i have written codes to INSERT to a database table - it has one primary keyso when i click the button, if there is already a row with primary key fields value as 10 and if i try to INSERT with the same value in the primary key field there will occur primary key constraintso , if i try to INSERT with the already existing primary key fields value, instead of INSERTing it should be UPDATEd without generating any errorplease help me
View 1 Replies View Relatedi want to insert data in database(sql server2000). there are some attributes in database which are present in two/three tables and these tables are related. e.g. when i create new user; it's userId and name should be inserted in 2 tables. how can i do it?
i think; it should be implemented through transaction statements but not much aware about these
When you import data using DTS into a table that has triggers - do the triggers fire off if there are triggers for on insert or on after insert?Thanks,--Micah
View 1 Replies View Related
i created an SSIS package to look for data in a table on another system and compare it with the table i have in system 2, if there are any changes to system 1 then it must apply them to system 2
My Problem:
It scans through my table and finds all the correct records to insert, but when it has to insert the new data into System 2 table i keep on getting violation and contraint errors because of the primary key and foreign key constraints. how can i get around this, or does anyone have an alternative solution for me.
Total Specification Requirements:
i have 2 systems both running SQL Server. everytime data gets updates in system 1, the same change needs to be made in system 2. The databases and tables are identical.
Any Help would be graetlty appreciated
Kind Regards
Carel Greaves
Hello everyone, My web application uses SQL Server database and I am connecting via standard SqlConnection object and running stored procedures using SqlCommand object. In one of my page, I have data coming from 2 different tables. Now , data from 1 table comes as only single record. But from other table it comes as multiple records. Meaning, data that I read as 1 record, goes to different textbox and dropdown controls on page. Data that comes in multiple rows, I am binding that data with DataGrid. Now, in aspx page data from both table can be updated and on aspx page I only need to provide a single save button. Now, I am not sure how to save/insert/update a single row in 1 table and multiple rows in another table in 1 transaction. I thought of stored procedure. But I don't think its straightforward with stored procedures since table with multiple records, I am not sure how to pass all the records in stored procedure's arguments.Is there any way that I can control whole transaction in ASP .NET? Thanks,Ujjaval
View 5 Replies View RelatedHey guys up until now i've only inserted data into a single table. Now I have a form that collects information over a span of three forms. Each form has a table related to it and these three tables are related to each other.
What I want to know is:
1)How do you go abouts inserting data into multiple related tables that have constraints on them?
2)Would you use a stored procedure in an instance like this?
3)At what stage would you execute the sql queries. I assume you do this once you have collected all the required information as opposed to: Enter info into form1, submit form1 data to database... enter info into form2, submit form2 data into database etc
Any help would be greatly appreciated!
Say for instance I have three related tables.
table1
------
tbl1_id
tbl1_data1
tbl1_data2
table2
------
tbl2_id
tbl2_data1
tbl2_data2
table3
------
tbl3_id
tbl3_data1
tbl3_data2
table1 has a one-to-many relationship with table2
table3 has a one-to-one relationship with table2
Hi all,we are developing an internal application that involves "Timesheets","Support" and "Project Management".Imagine that there are 3 different databases for the above scenario,under SQL Server 2000.My task is to create one or a few table triggers forINSERT/UPDATE/DELETE operations. For example:- if a row is added in Table A of "Timesheets" database, then Table Bof "Project Management" needs to be updated.The concept is clear i think. The question is how we do the above.Note that I am a new progremmer to SQL Server (I have been dealingwith Oracle so far), and I don't know how we programmatically connectto different database within a trigger, how do we check thepriviledges etc.Can someone help me?ThanksChristos Andronicou
View 1 Replies View RelatedI have 3 tables (accnt, jobcost, and servic15). all with the same fields (code, jno, ven, date). I need to insert the data from these tables into another table called dummy with the same fields, in one statement or query.
View 3 Replies View Relatedi have such a error in my sql server db
i examined arabic_ci_as and SQL_Latin1_General_CP1256_CI_AS
but in my web pages that uses utf-8 codepage that retrieves data using ado and asp scripting the ouput or inserted that dispalyed in both of query analyzer and Enterprise manager replaces or display '?????' for characters .
i am using nchar and nvarchar and ntext
My requirement is before inserting new data, we need to delete the old data based on the input in 4 tables.
For this one I need to write 4 individual delete statements.
Is it possible to delete rows from multiple tables in single statement in SQL Server 2012 by using joins?
[URL] .....
I am looking similar, I tried by keeping 4 table aliases in delete statement but it is throwing synatx error
In one store procedure I do insert same data into two tables (They have the same structure): OrderA and OrderB
insert into OrderA select * from OrderTemp
insert into OrderB select * from OrderTemp
And then got an error for code below.
"Multi-part identifier "dbo.orderB.OrderCity" could not be bound
IF dbo.OrderB.OrderCity=''
BEGIN
update dbo.OrderB
set dbo.orderB.OrderCity='London'
END
I am trying to insert data into two tables with a SSIS package. One table has a foreign key relationship to the other table's primary key. When I try to run the package, the package will just seems to hang up in bids. I have found two ways around the issue but I don't like either approach. Is there a way to set which table gets insert first?
If I uncheck the check constraints option on the child table, the package will run very quickly but this option alters the child table and basically disables the constraint. I don't like this option because it is altering the database.
The second approach is to set the commit level on both tables to say 10,000 and make sure that the multicast component has the first output path moved to the parent table. I don't like this option because I am not sure if the records are backed out if the package should abend after records have been committed.
I am trying to insert data into two different tables. I will insert into Table 2 based on an id I get from the Select Statement from Table1.
Insert Table1(Title,Description,Link,Whatever)Values(@title,@description,@link,@Whatever)Select WhateverID from Table1 Where Description = @DescriptionInsert into Table2(CategoryID,WhateverID)Values(@CategoryID,@WhateverID)
This statement is not working. What should I do? Should I use a stored procedure?? I am writing in C#. Can someone please help!!
From my query I am getting results like below in one of the column:
'immediate due 14,289.00
04/15/15 5,213.00
05/15/15 5,213.00
06/15/15 5,213.00
07/15/15 5,213.00
08/15/15 5,213.00
09/15/15 5,213.00
10/15/15 5,213.00
11/15/15 5,210.00'
this same type of many rows are there (i just mentioned one) but having same pattern with tabs as delimiter in between dates and amount.
I need something that shows Date on one side representing particular amount on the other
For Immediate Due it will be current date and the amount besides it.
how can I achieve this.
I have a text file which has rows 7 rows.I want to insert the data into SQL table using ssis In text file we have a column which has values as Y or N...I wanted to take only those rows which are Y...But we have only 6 rows in SQL table.It does not have the column with Y or N.
View 2 Replies View RelatedI need to deploy the trigger in database whenever new DB is creating on the server.
View 6 Replies View RelatedWriting to tables created by regular users on MSSQL2005
I have users creating tables through an application, I gave them ddl_admin, datareader, datawriter. They can create tables but cannot insert/update data (to their own tables), I cannot insert data either using Access or any other application to those tables created by them (under dbo schema) Is there something I am missing with permissions? Thank you very much
I am able to use user data types for creating local temporally tables in store procedures, but I receive an error at run-time about the user data type.
Msg 2715, Level 16, State 7, Procedure SP_SAMPLE_TEST, Line 4
Column, parameter, or variable #1: Cannot find data type D_ORDER_NUMBER.
The same user data type is used as parameters in several other store procedures, and they work perfect. It is also used in several table definitions.
What am I doing wrong?
Thanks in advance to any one who can help me with this problem.
Diego Sierra
I have a scenario, need to create SQL server Tables dynamically.
I Have multiple xml data file on a particular location, and want to load those XML data into sql server tables, but he metadata of each xml data files are not same.
Hence the approach is that,
1. Pick first file from that location
2. Create a table according to that xml data file metada
3. load data on newly created table.
4. Pickup the next xml data files.
5. loop through, till the XML data files are exists on that location.
How can I delete tables in a SQLServer Database automatically (at the moment I just do it using Micorosft SQl Management Studio Express manually) But its important that I can do that automatically, the best thing would be if I can do that from a .NET programm. thanks in advance,mulata
View 6 Replies View RelatedIs there a way to automatically delete tables in a database? I want to delete tables with a specific prefix in their names (that I assign to them) based on their age. Is there a way to do this automatically while keeping the tables that I don't want to touch?
joe
I was wondering if there is a way to schedule the deletion of temporary tables that are over a certain age. Right now, temporary tables are being created, but unless I delete them manually, they end up building up and just taking up a lot of space.
View 5 Replies View RelatedHi,
I need help in looking at sample script to perform automatic creation for weekly partitioned tables and also update the partitioned view accordingly.
Any helps are very much appreciated!
Thanks,
G
When creating a new row or record in the Master table (by an Adapter update), how do I then populate new records in 15 different Child tables?
These Child tables are attributes of the Master table. I am sure there is more than one way to do this...
Thanks
I am ultra new to this so thanks in advance for any help.
I was trying to create a connection to a database that I created in SQL Express. I am essentially trying to submit three attributes to the existing database from a table that consists of three textboxes and a submit button. I would like all of the code to be in the head of the page (because that is the standard here) so I wanted to know what the connection string should be in Visual Basic 2005 Express to establish a connection on the same machine. I'm not sure about the connection string, but I am also not sure about a lot of the code. Also, the Using clauses seem to give me an error (where should it go?). This is what I have in the head of the page (visual C# by the way). Also, I got this from http://www.csharp-station.com/Tutorials/AdoDotNet/Lesson03.aspx :
Using System.Data;Using System.Data.SqlClient;protected void Button1_Click(object sender, EventArgs e){SqlConnection conn = new SqlConnection("Data Source=(local);Database=CustomersDB;Trusted Connection=true");conn.Open();string insertstring = @" insert into Catagories (CustomerID, CustomerName, CustomerEmail) values (" + textbox1.value + ", " + textbox2.value + " + ", textbox3.value");SqlCommand cmd = new SqlCommand(insertstring, conn);Sql.ExecuteNonQuery();conn.close();}
If there is absolutely any insite into the problems with my code, I really appreciate it.
I am having some trouble populating a table with values from other tables:
I am creating the stored proc as follows:
CREATE PROCEDURE make_temp_stat (@from datetime, @to datetime)
AS
DROP TABLE tempTable
Create tempTable
(
NumApplications (int),
NumStudents (int),
NumTeachers (int)
)
//Then I insert the values into the table as follows
INSERT INTO tempTable (NumApplications) SELECT Count(*) FROM [dbo].[CASE_APPLICATION] WHERE (OPEN_DT>= @from AND OPEN_DT <= @to)
INSERT INTO tempTable (NumStudents) SELECT Count(*) FROM [dbo].[CASE_STUDENTS] WHERE (APP_DT>= @from_dt AND APP_DT<= @to_dt)
INSERT INTO tempTable (NumTeachers) SELECT Count(*) FROM [dbo].[CASE_TEACHER] WHERE (JOIN_DT>=@from_dt AND JOIN_DT<= @to_dt)
GO
Nothing happens when I run this stored proc. Can sombody point out what exactly is wrong over here?
Ok, I'm really new at this, but I am looking for a way to automatically insert new records into tables. I have one primary table with a primary key id that is automatically generated on insert and 3 other tables that have foreign keys pointing to the primary key. Is there a way to automatically create new records in the foreign tables that will have the new id? Would this be a job for a trigger, stored procedure? I admit I haven't studied up on those yet--I am learning things as I need them. Thanks.
View 4 Replies View RelatedI just got started with ASP.NET and SQL Express a few days ago. I have a little bit of SQL (MySQL) experience, but nothing professional. I'm trying to build a toy application just as a refresher for SQL and to learn a few more things.Anyway, I've got two tables:Users:user_id, user_nameRegistration:reg_id, user_id, fname, lname, passwdNow, when someone new registers on the site, I need to update both tables. When I add a user to the Users table, the user_id is autoincremented. I'd like to get that user_id, somehow, so that I can then update the registration table with the correct user_id.What would be the "best" way to do this? SQL query? Stored procedure? Trigger?
View 2 Replies View RelatedHello all... I'm working on a C++ Windows service that writes to a SQL Server database. I consider myself quite a novice at SQL Server, but I have played around with it over the years... Performance is going to be a concern with this project.
Let's say...
Table A has columns PkA(identity), Stuff(text), FkB (Table B's Pk)
Table B has columns PkB(identity), MoreStuff(text)
I'll be executing SQL statements from my service - INSERTs, etc...
What's the most efficient way to write to these two tables? The immediate challenge I have is getting that PkB value after inserting Table B and using it for Table A's FkB.
Is there a way I can insert into both tables with one SQL statement?
Thanks!! Curt.