Multiple Reference To Mutiple Tables
Aug 23, 2007
Not sure if the title describes my situation or not.
Simplified example is:
I have an [Employee] table with EmpCode, EmpName
I have a second table [NewHires] that has: HireDate, EmpCode, Addedby
Both EmpCode and Addedby contain EmpCode referring to the Employee table.
I wish an output similar to:
New Employee (from EmpCode in NewHire), Hired on (From HireDate), Hired By (from Addedby)
My problem is with an Employee.EmpCode=NewHires.Empcode or Employee.EmpCode=NewHires.Addedby in the Where clause or Join part of the SQL I don't know how to get EmpName from the Employee table twice but using two different EmpCode as the reference.
Thanks in advance - Shawn
View 2 Replies
ADVERTISEMENT
Jun 12, 2007
Consider a situation. There is a table of submitted 'documents'. They have some attributes. There are assignments to process the things, which have a date they were created. Finally there is a price list which specifies the price according to document features and date, so that the assignment to process a document created at different time will have a different cost. In other words, there is a relation
(assignment->document.attribute(s) + assignment.date) -> pricelist.price
Creating relations has the integrity advantages: it is not possible to create an assignment, which price is not defined in the pricelist; precludes the pricelist entry removal if it is referred by any assignments.
Should a view, which combines all the foreign fields into one virtual table, be created to make establishing the reference possible?
View 2 Replies
View Related
Sep 5, 2007
Hello All
Firstly thanks a lot Phil and Jamie on such a helpful article on "Checking to see if a record exists and if so update else insert"
Here is my question
I have about 10 tables and there respective working tables
For examples: A, B, C, D, E.... and WorkA, WorkB, WorkC....
Notes:
1) When I execute a package these work table (Work A, WorkB ...) get populated with certain rows say about 5
2) Its not that all the work table are populated on every execution.
3) Tables A, B, C... have thousands of records in it.
4) Work table is of same structure as there parent table..Like WorkA same structure as A.....
5) The table A and WorkA and as on... are linked with a KeyID
Now I want to build a SSIS package that can
1) Get the the data from these multiple tables(WorkA, WorkB...)
2) Process each row of these tables WorkA, WorkB..
3) Depending upon the KEYID of WorkA., WorkB.. etc Update a Flag colunm of table A, B...where the KeyID is equal to KeyID of Work Table
4) After updating insert that processed row of Work A, WorkB ...into Table A, B..
I can do this if I have one source table and one destination table. Here i have some say 10 randomly source tables to respective random destination .
All I could think of creating 10 different packages as adviced in Jamie's article. But I am sure there might some other alternative.
Can somebody advice me the best practice of doing this. Thanks a lot in advance
View 5 Replies
View Related
Oct 12, 2004
I have a lookup table called States, I have multiple other tables that use this lookup table, but I can only relate to one other table. My question is if I change a state name how do I enforce that change to the other tables that CANT be related? I know it is a design flaw and need some kind of joining table. I am having difficulty understanding and incorporating first normal form in my database. Here is what i have so far.
Example:
Table 1
========|============|=============|========|
CustomerID BillingAddressID ShippingAddressID OtherFields
========|============|=============|========|
Table 2
===========|======|========|
BillingAddressID StateID OtherFields
===========|======|========|
Table 3
=============|======|========|
ShippingAddressID StateID OtherFields
=============|======|========|
Table 4
======|====|
StateID Name
======|====|
How do I relate table 4 with table 2 & 3 for referential integrity? Or create a joiner table?
View 7 Replies
View Related
Mar 21, 2008
Hi! I have a general SQL CE v3.5 design question related to table/file layout. I have an system that has multiple tables that fall into categories of data access. The 3 categories of data access are:
1 is for configuration-related data. There is one application that will read/write to the data, and a second application that will read the data on startup.
1 is for high-performance temporal storage of data. The data objects are all the same type, but they are our own custom object and not just simple types.
1 is for logging where the data will be permanent - unless the configured size/recycling settings cause a resize or cleanup. There will be one application writing alot [potentially] of data depending on log settings, and another application searching/reading sections of data.
When working with data and designing the layout, I like to approach things from a data-centric mindset, because this seems to result in a better performing system. That said, I am thinking about using 3 individual SDF files for the above data access scenarios - as opposed to a single SDF with multiple tables. I'm thinking this would provide better performance in SQL CE because the query engine will not have alot of different types of queries going against the same database file. For instance, the temporal storage is basically reading/writing/deleting various amounts of data. And, this is different from the logging, where the log can grow pretty large - definitely bigger than the default 128 MB. So, it seems logical to manage them separately.
I would greatly appreciate any suggestions from the SQL CE experts with regard to my approach. If there are any tips/tricks with respect to different data access scenarios - taking into account performance, type of data access, etc. - I would love to take a look at that.
Thanks in advance for any help/suggestions,
Bob
View 1 Replies
View Related
Aug 22, 2007
I have facing a design problem and unable to justify which design to choose for my data model.
Usually, what we have is like data tables and reference tables to store data in those data tables.
My database has tables with 20-30 columns in them. And most of them (though not all of them), stores data from some reference tables. Meaning each column has an associated reference table where it stores possible list of values for that particular column. Sort of data domain for that column. FYI, My database is related to medical field.
For example, A table that has a varchar(40) column called "Differentiation". It can only store values from following list:
- Undifferentiated
- Moderate
- Poor
- Poor - Moderate
- Moderate - well
Now, to implement this, simple solution would be to have a reference table where I can store all these possible values...And then have just a reference of each data item into my "Differentiation" column in the table.
This is simplest and probably the best solution for such thing and i can also have referential integrity implemented for this.
But now if we look at the bigger picture, my database is growing and I have about 80 tables which I need to create where most of the columns will have different reference tables like I mentioned above. Approximate number of reference tables is 300 tables. All the reference table will have same structure, with different values for different columns.
Now, what seems to me is, because the table structure is same for every column, rather than having 300 different tables, I can only have 2 tables, where I can put all these reference values into these 2 tables.
Like,
Table 1 :
This table can have name of the reference table like "differentiationlist" etc.
Table 2:
It has reference to the reference table list in Table 1 discussed above and all the values that are part of that reference table can go in this table with its reference.
But problem with this is, because all the reference tables are in these two tables, I don't know how to implement referential integrity in this design.
Does anyone have any idea or solution for situation like this?
Thanks,
Ujjaval
View 14 Replies
View Related
Dec 12, 2005
I need to write a query that requires respective fields referencing from multiple tables.
For example, here are the tables:
Main Table:
InfoID Team1 Player1 Team1
Table: Player_ref
Player Team_Player_ref Player1 John doh
Table: Team_ref
Team Team_Player_ref Team1 My Team
Ideal result Table from query:
InfoID Count John Doh 1 My Team 2
Any suggestion to creat the Ideal Results table from query?
Normally, I could do it if it only referenced from 1 table, I would do an inner join, however, since there are 2 referenece table, doing inner join wouldn't work. A proposed suggestion would certainly be nice. Thanks in advance.
--daydreamstuck at the current problem
View 6 Replies
View Related
Sep 1, 2004
I have two tables that are reference to each other by foreign key, now I would like to alter the table so the it doesn't reference each other any more. What is the syntax to alter a table so that the fk field doesn't reference a table anymore. Thanks in advance for the help.
View 1 Replies
View Related
Jul 14, 2006
Hi ,
I have the following structure:
Item table
----------
ItemId
ItemName
Item Transaction Table
----------------------
TransactionId
GiverItemId
SenderItemId
the data is somewhat like this:
Item table
__________
1abc
2xyz
3pnr
4rew
5dds
6djs
7dsf
Item Transaction table
---------------------
112
224
343
437
Now i have a reauirement to build a stored proc in which all the transactions starting from one transaction like, if i want to know the chain for item no 2 it shall give the following result:
24
43
37
if i want to know the chain for item no 3 then it shall give following
37
if i want to know the chain for item no 1 then it shall give following
12
24
43
37
Please help.. Its urgent.....
Thanks
View 1 Replies
View Related
Sep 25, 2007
Hi there...
Sounds like a veryy silly question but I somehow got stuck with this & cant think anymore
Basically, I have got a table 'Order_Product' with Clustered Primary Key of columns 'Order_Number' & 'Product_Id'.
I am creating a new table Order_Discussion' with column 'Product', which is a Foreign Key & should reference 'Order_Number' & 'Product_Id' of table 'Order_Product'.
I am trying something like..
Create Table Order_Discussion (
Discussion_Id int,
Product int Foreign Key References Order_Product(Order_Number, Product_Id)
)
but its giving error..
More than one key specified in column level FOREIGN KEY constraint, table 'Order_Discussion'.
Any help will be greatly appreciated.
Cheers!!
View 6 Replies
View Related
Jun 18, 2008
Ok so I am trying to update rows which have a rNumber in the range of a specific lookup table fields. Example.
Table A (source table)
----------
cn, rNumber
null, 223
null, 9999
null, 12345
Table B (Reference table)
-------------
cn, start_r, end_r
A, 1, 300
B, 9000, 10000
C, 11000, 13000
I want to find the 'cn' for tableA.rNumber where it exists in the range of tableB.start_r and tableB.end_r
how do I do it??
-Chris Varick
View 3 Replies
View Related
Jan 14, 2008
I'm trying to create two tables, Admins and SecurityGroups, I want to have the ability to assign a security group to an admin so that I don't have to specify all the rights individually to every admin. So, I have the securityGroupID in the Admins table and I reference the SecurityGroups table for data integrity. On the other hand, for audit purposes, I also want to record which admin created the security group so I have the AdminID in the SecurityGroups table and I reference the Admins table for data integrity. Of course when I try to create any of those tables I get an error message that the other table doesn't exist. I wonder if this can be done without having to remove the references constraint from one of the tables and then using alter table. Here's an example below to make it clearer:
CREATE TABLE Admins
(
AdminID INT NOT NULL PRIMARY KEY,
SecurityGroup NULL REFERENCES SecurityGroups (SecurityGroupID)
)
CREATE TABLE SecurityGroups
(
SecurityGroupID INT NOT NULL PRIMARY KEY,
CreatedBy INT NOT NULL REFERENCES Admins (AdminID)
)
Any suggestions are highly appreciated...
View 7 Replies
View Related
Nov 2, 2007
Hi,
I don't know if this is possible, i believe not, so I'm here to ask the experts if is possible to have a foreign key constraint that references the key of one of two tables.
Like this:
I have 3 tables: TABLE X, TABLE A and TABLE B
Is it possible to the FK on TABLE X refernce the PK of TABLE A OR TABLE B?
If yes, how can I do this?
If not, I need to have a fourth table, so TABLE X references TABLE A and TABLE Y references TABLE B.
Thanks!
View 4 Replies
View Related
Jul 28, 2015
I have 2 tables, OBJECTS and LINKS both have common field(OBJECT_ID).
I need to update certain records in table OBJECTS only if they meet certain criteria in table LINKS.
How do I go about doing this..???
View 8 Replies
View Related
Jun 16, 2015
I have a requirement where in i have around 15 different flat files , filenames are fixed but folder path can be changed(i think i should use a variable for folder path). These 15 files data should go to their respective tables in the database.
Whether I need to create separate data flow task for each file or separate package? In addition to these, example : while importing product data into product table, if product ID already exists, we need to ignore it and upload only the new records.
View 4 Replies
View Related
Aug 25, 2015
I have an excel file that has multiple sheets and I need to import data from each separate sheet to a separate table using SSIS.
E.g. Sheet A data should go to Table A and Sheet B data should go to Table B and so on. Is it possible to do this with out using script task.
View 6 Replies
View Related
Sep 8, 2006
I have just taken over the job of sorting out a rather poorly designed database. It looks like it was 'upsized' from an access database to the SQL server. The SQL server is the 2000 version.
Now I am trying to generate a report of what the students in the database are owing by referencing the Receipt table and then all the available payment methods and allocations. I was wondering if there was anyway to work out data being displayed twice (Let me demonstrate)
Note1: All the tables are linked by a key of ReceiptNo. From what I can see there is a table for every payment type and allocation but no link between the two other then the receipt number.
Using the query:
SELECT T_Receipt.ReceiptNo, T_cheque.Amount AS Chq_Amount, T_credit.Amount AS Cre_Amount, StandingOrder.Amount AS Stn_Amount,
T_BankTransfer.amount AS Bnk_Amount, T_cash.TotalAmount AS Cas_Amount, T_RentPayment.AmountPayed AS Ren_Paid,
T_AdminPayment.AmountPaid AS Adm_Paid, T_InternetBilling.Total AS Int_Paid, T_Utilities.AmountPaid AS Util_Amount,
T_InvoicePayment.amountPaid AS Inv_Paid, T_OtherPayments.paymentAmount AS Oth_Paid, T_parkingBill.paymentAmount AS Prk_Paid,
T_TelephoneBills.TelephoneCredit AS Tel_Paid, T_DepositPayment.[Deposit payment] AS Dep_Amount, T_Receipt.cancelled AS Canceled,
T_Receipt.RemittanceReceiptNo AS Rec_Ref, T_Receipt.Student
FROM T_Receipt INNER JOIN
T_DepositPayment ON T_Receipt.ReceiptNo = T_DepositPayment.receiptNo LEFT OUTER JOIN
T_RentPayment ON T_Receipt.ReceiptNo = T_RentPayment.RentPaymentNo LEFT OUTER JOIN
StandingOrder ON T_Receipt.ReceiptNo = StandingOrder.ReceiptNo LEFT OUTER JOIN
T_TelephoneBills ON T_Receipt.ReceiptNo = T_TelephoneBills.ReceiptNo LEFT OUTER JOIN
T_parkingBill ON T_Receipt.ReceiptNo = T_parkingBill.ReceiptNo LEFT OUTER JOIN
T_OtherPayments ON T_Receipt.ReceiptNo = T_OtherPayments.ReceiptNo LEFT OUTER JOIN
T_InvoicePayment ON T_Receipt.ReceiptNo = T_InvoicePayment.receiptNo LEFT OUTER JOIN
T_cash ON T_Receipt.ReceiptNo = T_cash.ReceiptNo LEFT OUTER JOIN
T_AdminPayment ON T_Receipt.ReceiptNo = T_AdminPayment.ReceiptNo LEFT OUTER JOIN
T_BankTransfer ON T_Receipt.ReceiptNo = T_BankTransfer.receiptNo LEFT OUTER JOIN
T_Utilities ON T_Receipt.ReceiptNo = T_Utilities.receiptNo LEFT OUTER JOIN
T_credit ON T_Receipt.ReceiptNo = T_credit.ReceiptNo LEFT OUTER JOIN
T_cheque ON T_Receipt.ReceiptNo = T_cheque.ReceiptNo LEFT OUTER JOIN
T_InternetBilling ON T_Receipt.ReceiptNo = T_InternetBilling.ReceiptNo
GROUP BY T_Receipt.Student, T_Receipt.ReceiptNo, T_cheque.Amount, T_credit.Amount, StandingOrder.Amount, T_BankTransfer.amount, T_cash.TotalAmount,
T_AdminPayment.AmountPaid, T_InternetBilling.Total, T_Utilities.AmountPaid, T_InvoicePayment.amountPaid, T_OtherPayments.paymentAmount,
T_parkingBill.paymentAmount, T_TelephoneBills.TelephoneCredit, T_Receipt.cancelled, T_Receipt.RemittanceReceiptNo,
T_DepositPayment.[Deposit payment], T_RentPayment.AmountPayed, T_Receipt.Student
HAVING (T_Receipt.Student LIKE N'06%')
Which gives a result of:
RecNo.
30429
Cheque
250
Deposit
250
30429
679.98
250
This is fine but when I do analysis on this it appears as though the student has paid two deposit payments. I was wondering with out querying each table independently from an application if there was a criteria to specify that I only get one deposit result.
So as such say, give me all the payments but I only want one result from the other tables. I though about discrete but that wouldn't work here.
View 3 Replies
View Related
Nov 15, 2006
Hi!
I have 7 source databases and one target database, all using the same structure. The structure is made of 10 tables, with foreign key constraints.
I need to merge the source databases into the target (which won't have any data before that process, but will already have the correct schema), and to keep the relationships between the records.
I know how to iterate over the source databases (with SMO foreach), but I'd like to know if someone can advise the best copy method for that context in SSIS ? (I don't want to keep the primary keys, but I need to keep the relationships...)
Any pointer most welcome!
best regards and thanks
Thibaut
View 1 Replies
View Related
Jun 27, 2006
I have a couple of hundred flat files to import into database tables using SSIS.
The files can be divided into groups by the format they use. I understand that I could import each group of files that have a common format at the same time using a Foreach Loop Container.
However, the example for the Foreach Loop Container has multiple files all being imported into the same database table. In my case, each file needs to be imported into a different database table.
Is it possible to import each set of files with the same format into different tables in a simple loop? I can't see a way to make a Data Flow Destination item accept its table name dynamically, which seems to prevent me doing this.
I suppose I could make a different Data Flow Destination item for each file, in the Data Flow. Would that be a reasonable solution, or is there a simpler solution, or should I just resign myself to making a separate Data Flow for every single file?
View 9 Replies
View Related
Sep 10, 2014
I am using vs 2010 to write my dtsx import scripts.I use a script component as a source to create a flat file destination file.Everything have been working fine,but then my development machine crashed and we have to install everything again.Now when i use the execute package utility to test my scripts i get the following error:
Error system.NullReferenceException: Object refrence not set to an instance reference.
In PreExecute section
TextReader = new system.io.streamreader(" file name")
In the CreateNewOutputRows:
dim nextLine as string
nextLine = textReader.ReadLine
[code]...
is there something which i did not install or what can be the error?
View 0 Replies
View Related
Mar 1, 2007
Hello
I am building a survey application.
I have 8 questions.
Textbox - Call reference
Dropdownmenu - choose Support method
Radio button lists - Customer satisfaction questions 1-5
Multiline textbox - other comments.
I want to insert textbox, dropdown menu into a db table, then insert each question score into a score column with each question having an ID.
I envisage to do this I will need an insert query for the textbox and dropdownlist and then an insert for each question based on ID and score.
Please help me!
Thanks
Andrew
View 9 Replies
View Related
Sep 3, 2014
How to insert single row/multiple rows into multiple tables by using single insert statement.
View 1 Replies
View Related
Feb 15, 2008
I need to be able to bulk insert a bunch of tables from their corresponding flat file. I have created an XML file (see below) which has the file name/table name pair at each node. I then created a ForEachLoop task and used the Node enumeration type and the following OuterXpathString: ReferenceFiles/File. At this point I get lost. How do I pass the 2 inside node values (file name and table name) to variables which I can then use as expressions for the bulk insert task inside the Foreach?
Here is XML file:
Code Snippet
<ReferenceFiles>
<File>
<FileName>Ref_Categories.txt</FileName>
<TableName>Ref_Categories</TableName>
</File>
<File>
<FileName>Ref_Configs.txt</FileName>
<TableName>Ref_Configs</TableName>
</File>
</ReferenceFiles>
Thanks.
View 1 Replies
View Related
Nov 29, 2007
I used the data export wizard to export a single table to a single flat file (multiple wasn't allowed). I saved the package as a *.dtsx file which I'm attempting to edit to add the additional tables.
Creating additional sources is fairly easy copy of the first source and change to the table name.
I've tried copying the destination connection and changing to a new text file, but can't get past having to add each column manually to the new destination.
How can I duplicate the mapping that must be taking place in the wizard in the *.dtsx editing environment?
This seems like a simple / common task, but I've been unable to find a solution.
Thanks, Richard
View 1 Replies
View Related
May 31, 2007
I am trying to query the Topics in my discussion forum...The Topic contains a "last_poster_id" and a "author_id" I need the username and userid for both "last_poster_id" and "author_id" in the table "aspnet_Users"How do I do this?I would guess I need to use sub select statements. Can someone help me?
View 12 Replies
View Related
Dec 21, 2007
Hi,
I am trying to build search engin with 11 parameters in 4 different tables in the database.
For example:
In search.aspx I have 11 textboxes namely
nameTextbox, phoneTextbox, nationalityTextbox, ageTextbox etc.
And in the result.aspx page I have gridview which post data from the database if the search match.
I wrote this stored procedure. P.S please ignore the syntax.
@name var(30),
@nationality (30),
@phone int,
etc
as
Select a.UserId, b.UserId, c.UserId FROM Table1 a, Table2 b, Table3 c
WHERE
name LIKE '%' @name '%'
OR nationality LIKE '%' @nationality '%'
OR phone LIKE '%' @phone '%'
etc
But I got an error when I am trying to execute this code because the nulls values so I wrote
1 @name var(30),
2
3 @nationality (30),
4
5 @phone int,
6
7 etc
8
9 as
10
11
12
13 Select a.UserId, b.UserId, c.UserId FROM Table1 a, Table2 b, Table3 c
14
15 WHERE
16
17 name LIKE '%' ISNULL(@name, '') '%'
18
19 OR nationality LIKE '%' ISNULL(@nationality,'') '%'
20
21 OR phone LIKE '%' ISNULL(@phone,'') '%'
22
23 etc
24
25
Also the error still exist.
What is the best way to search for multiple parameters in multiple tables ?
Thanks in advanced
View 4 Replies
View Related
May 23, 2008
Hello,
I am in the progress of designing a new section of my database and was thinking of creating a hole new database instead of just creating tables inside the database. My question is can you JOIN multiple tables in an SQL Statement from multiple databases. Ie, In the Management program I have a database called 'Convention' and another one called 'Services', inside the two databases there are many tables. Can I link say tblRegister from Convention to tblUser in Services?
Thanks
View 3 Replies
View Related
Nov 4, 2004
i want to select all the user tables within the database and then all the records with in each table.
plz tell me one query to do this.
ex: suppose x and y are user tables and x contain 10 records and y contains 20 records . i want a query which displays all 30 records together.
View 1 Replies
View Related
Jan 20, 2006
What is the simplist/correct way to delete multiple records from multiple tables. Any help appreciated. Thanks! (Yes, I'm totally new to this.)
delete dbo.tblcase
where dbo.tblcase.case_id in ('299760', '299757', '299739', '299732', '299902',
'299901', '299897', '299894', '299873', '299872', '299870', '299865', '299860',
'299858', '299854', '299846', '299838', '299834', '299821', '299813', '299803')
delete dbo.tblcaseclient
where dbo.tblcaseclient.case_id in ('299760', '299757', '299739', '299732', '299902',
'299901', '299897', '299894', '299873', '299872', '299870', '299865', '299860',
'299858', '299854', '299846', '299838', '299834', '299821', '299813', '299803')
delete dbo.tblcaseinformation
where dbo.tblcaseinformation.case_id in ('299760', '299757', '299739', '299732', '299902',
'299901', '299897', '299894', '299873', '299872', '299870', '299865', '299860',
'299858', '299854', '299846', '299838', '299834', '299821', '299813', '299803')
delete dbo.tblcaselawyer
where dbo.tblcaselawyer.case_id in ('299760', '299757', '299739', '299732', '299902',
'299901', '299897', '299894', '299873', '299872', '299870', '299865', '299860',
'299858', '299854', '299846', '299838', '299834', '299821', '299813', '299803')
delete dbo.tblcaseprosecutor
where dbo.tblcaseprosecutor.case_id in ('299760', '299757', '299739', '299732', '299902',
'299901', '299897', '299894', '299873', '299872', '299870', '299865', '299860',
'299858', '299854', '299846', '299838', '299834', '299821', '299813', '299803')
View 1 Replies
View Related
Jun 1, 2007
Hi,
I have searched but not found quite the best way to look at this so far..
I have an application that outputs data to several text files (up to 30). These have commonality by an object name, but then contain completely different column data.
In DTS I had each of the source text file connections going to one OLE DB connection and then individual transform data tasks pointing to the one OLE DB connection.
Looking at SSIS, it would appear that I would need to have one source and one destination for each of these and therefore 30 parallel data flows?
Just wondering if there is a neater way of doing this??
It is a regular data import that happens a few times a day - the text files are named the same as the SQL tables - ie app_userdata.txt goes to app_userdata table.
Hope that explains ok and thanks in advance.
Mike
View 3 Replies
View Related
May 4, 2006
Hello,I have a query that I need help with.there are two tables...Product- ProductId- Property1- Property2- Property3PropertyType- PropertyTypeId- PropertyTypeThere many columns in (Product) that reverence 1 lookup table (PropertyType)In the table Product, the columns Property1, Property2, Property3 all contain a numerical value that references PropertyType.PropertyTypeIdHow do I select a Product so I get all rows from Product and also the PropertyType that corresponds to the Product.Property1, Product.Property2, and Product.Property3ProductId | Property1 | Property2 | Property3 | PropertyType1 | PropertyType2 | PropertyType3 PropertyType(1) = PropertyType for Property1PropertyType(2) = PropertyType for Property2PropertyType(3) = PropertyType for Property3I hope this makes sence.Thanks in advance.
View 3 Replies
View Related
Aug 12, 2006
Hello all,
Being still a relative newcomer to SQL Server (people may say I'm trying to take on too much being somewhat inexperienced once they read about the problem I'm trying to tackle, but alas...) I'm running into the following problem: I need to create tables in my user database on the fly (using Stored Procedures) so that each table can be created many times in the database but only once for every user. The tables should be named something like "username.Table1", "username.Table2" etc. as opposed to "dbo.Table1". I then want to use the stored procedure from .NET/C# in my web application, so that i can create the complete set of usertables for each of my clients.
Now, I tackled the stored procedure part (that is, it creates all the tables I need with all the parameters I want) and am able to use it from my web application (which took some time to learn but proved quite easy to do), but I cannot seem to get it coupled to the current user (instead of the dbo). Every time I trie, the tables are created as dbo.Table1 and when I try to create a new set, it gives a warning ("table with name such and so already exists..."). I made sure to log in as an authenticated user (using forms authentication) before trying to create the tables but this gives the aforementioned result.
What am I doing wrong? I use Visual Web Developer Express, SQL Server 2005 Express and IIS version 5.1
Please help :-D
Greetingz,
DJ Roelfsema
View 6 Replies
View Related
Jan 3, 2006
Hi there,
I have been at this problem for a few hours now using Net 2.0, and can't seem to find an answer.
I am using a asp:calendar that allows the user to pick multiple dates.
When the user is finished and hits the command button, I would like for those dates to be inserted into a table in my SQL DB called Dates.
The mutildate calendar works fine--, and I have placed all of the sates into an Array. And now have a loop for the array to print them out.
What I want to do, instead of printing, is insert them.
C anyone post some 2.0 code behind for insertion? I have found other "batch insert" code, but when I place in the VB, I get errors telling me it is no longer supported.
For example:
Dim sqlCon As New SqlConnection (no longer works in 2.0) since it doesn't recognize Sqlconnection.
Should I use a SqlDataSource? And if so, how do I prepare the Insert command for it?
Any and all help appreciated!
View 6 Replies
View Related
