I'm wondering if there is a control available for creating/modifying db tables through a web interface. I want for users to be able to add/remove, rename, and change the datatype of certain fields in a database table. I've been searching all day online if such a control exists in asp.net but haven't found anything.
Hi there,I need some help in order to reduce the amount of code needed toimplement the logging of INSERT and UPDATE in my application'sdatabase.In every table of my database I have four fields (CreatedBy,CreatedTime, ModifiedBy, ModificatedTime), these fields allow me totrace the users who create and modify the registers in my application.I know that I can add in every stored procedure a piece of code whouses the app user as a parameter, and insert these information in theregister. But I would like to go beyond these method. I imagine thereis a better way of doing it, and I don't like to use triggers. Isthere any method, within the SQLServer functionality, that allows meto automate this process?So, any help would be very appreciated!!Thank you very much in advance!
View 1 Replies View RelatedI'm trying to create an SSIS package that will do a straight data copy between databases. The problem is that the underlying schema of the origin may change and the requirement is that the transfer be table driven. i.e. the tables that are copied are listed in a table and there should be no human intervention when the schema changes.
I'm moving data between SQL Server and SQL Azure, so backup and restore doesn't work. Has to be an SSIS package.
What's the best way to deal with a changing schema in an SSIS package? Can I delete and rewrite the underlying XML for any tables that change? Do I need to do it programmatically with C#? Do I need to create the package from scratch each time?
Hi,I'm trying to create some tables dynamically based on the content of another table in the same database. I found a post that does what I want to do, but I can't get my code (that is similar to the post) to work.Given below is my code: 1 DECLARE @deptCode varchar(50), @numberOfDept int, @tableName varchar(MAX), @columnName varchar(MAX)
2 DECLARE @lengthDeptCode int, @lengthTableName int, @lengthColumnName int
3
4 SELECT @numberOfDept = COUNT(DISTINCT DeptCode)
5 FROM tbl_Department;
6
7 WHILE (@numberOfDept >=0)
8 BEGIN
9 SELECT @deptCode = DeptCode, @lengthDeptCode = LEN(DeptCode)
10 FROM tbl_Department;
11
12 SET @tableName = 'tbl_ProjectNumber'+@deptCode
13 SET @lengthTableName = LEN(@tableName)
14 SET @columnName = 'ProjectNumber'+@deptCode
15 SET @lengthColumnName = LEN(@columnName)
16
17 CREATE TABLE CAST(@tableName as char(@lengthTableName))
18 (
19 CAST(@columnName as char(@lengthColumnName)) int IDENTITY(1,1) NOT NULL
20 )
21
22 SET @numberOfDept = @numberOfDept - 1
23 END
This is actually my first time using SQL programatically so I'm guessing there are alot of problems with it. I just don't know what exactly. The error I get is:Msg 102, Level 15, State 1, Line 18Incorrect syntax near '@tableName'. Thanks.
Hi there,I am trying to generate tables names on the fly depending on another table. So i am creating a local variable containing the table names as required. I am storing the tables in a local variable called@TABLENAME VARCHAR(16)and when i say SELECT * FROM @TABLENAMEit is giving me an error and I think I cannot declare @TABLENAME as a table variable because I do not want to create a temp table of sorts.I hope I am clear.thanks,Murthy here
View 6 Replies View RelatedSuppose I have a table named table1 which has a field question_Id.There are many values for this field say 100,101,102.
Now I want to make a table with the field name 100,101,102 but the problem is that it is not fixed how many values are there for question_id.Here in this example I mentioned three(100,101,102) but it may be anything.How can I make a table with the field names this way?
Subhasish
My idea is to read in source and destination values from a table and using these values within a ForEach loop dynamically alter the source, destination and mapping on the data flow within the package. My reading on SSIS leads me to believe that these properties are not available for modification at run-time. Has anyone any ideas on how to accomplish this task. I have data in over 200 tables to import every 4 hours so I'd rather have to maintain 1 package rather than 200.
View 9 Replies View RelatedHi All,
I have a web site where the user can select from a list of databases (they're actually all the same structure, but have data unique to each client). Each database is a SQL 2005 database held in the App_Data folder. This all works a treat. Each database is simply named after the client it is holding data for, and the connection string for each is stored in a database that is accessed
However, what I need is a form where a database name can be entered, and I can then create a new database with the same structure as the others - including tables, indexes and stored-procedures. I know I can use the copy database wizard in the Management Studio, but that isn't an option to the users of this system (it's to be used on an intranet, and the page I'm trying to create will only be used by project managers - but these people won't have a clue when it comes to configuring databases themselves). I have scripted a database, but have no idea on how I can actually use this script from within Visual Studio and more importantly, don't know how to configue it to have the database name as a parameter.
I've also seen some examples that suggest using SQL-DMO, but haven't been able to work out how to use it and according to Microsoft it shouldn't be used for new developments (http://msdn2.microsoft.com/en-us/library/ms132924.aspx).
Anyway, I really hope someone is able to offer some assistance on this one as I've spent a whole day trawling forums and various web-sites trying to find a solution to this.
Thanks & regards,
Paul
I'm trying to read the folder which has flat files and transform it and put it another folder with the SAME original name.
I created a For Eachloop containter and assign it to variable infile. In the data flow I added some transform and flat file destination. currently its reading 3 files and output is only one file.
I would like to create dynamic flat file connection so that it'll create separate files (same name) in different folder.
Thanks,
Hi All,
I'm new to SSIS. I have a small problem. In our system we are trying to create flat files based on data from returned from different SQL stored procedures. The configuration settings for creating the flat files are stored in a SQL table.
For example, the config table will look like below :
Feed Id Feed Name Feed File Name Feed Stored Procedure Name
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
1 Biz C:Biz.txt dvx_GetBiz
2 PG C:PG.csv dvx_GetPG
3 NexT C:NexT.txt dvx_GetNexT
4 Amez C:Amez.csv dvx_GetAmez
The problem is dvx_GetBiz, dvx_GetPG,... all have different number and types of columns. So, I'm not able to create a dynamic Flat File destination depending on the columns returned from the corresponding stored procedure.
Is there a way to accomplish this ?
Pl. help.
Awaiting someones reply ASAP.
Thanks & Regards,
Vidya.
I hope some of you can point me in the right direction. I am new to all this and
working on a project using Cubes.
The cubes need to be generated in a dynamic fashion as the contents
(that means field) in the fact tables change change every quarter and are
based off of a external application.
The base strucutre stays the same though. So we thought we would export the
XMLA representation of the cube and then munge (word for alter) the data in the
XMLA file and send it someplace to create the new cube on a SQL 2005 server.
This seemed easy enough but I have found no real information on how to
run some piece of code that would create the cube based on this XMLA code.
On another note is there a parsing object for the XMLA so i dont have to right one from
scratch?
Thanks,
Bob
Hi All, i've been reading this article http://rafael-salas.blogspot.com/2006/12/import-header-line-tables-_116683388696570741.html
in regards to creating an excel spreadsheet dynamically in SQL Server 2005 SSIS. However, i'm constantly getting an where the tab is created but not being populated. Can somebody post up a clearer example?
The problem I'm trying to solve is to automate the export of a query onto a new dynamic spreadsheet each time I run this SSIS package.
Any help would be greatly appreciated.
Thank you.
Hello. I'm trying to create a new login and username inside a trigger using variables.
This code works:
create login testUserName with password = 'testPassword'
The problem comes when I try to use variables for the username and password like this.
create login @username with password = @password
Does anybody know how to get around this problem?
BTW, the error message it gives is this, but I really doubt that semicolons have anything to do with it. If I literally type my data in the create login call it works fine, but when I use variables it doesn't.
Msg 102, Level 15, State 1, Line 14
Incorrect syntax near '@username'.
Msg 319, Level 15, State 1, Line 14
Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.
I have a reqirement where in i need to create a Database dynamically in SSIS, Database name is given in table.
View 5 Replies View RelatedI have a requirement where in i need to create a Database dynamically in SSIS, Database name is given in table.
View 3 Replies View RelatedIs there a way to find out when the last modification or change to data in a table occured ? How about the last change of a view ?
We'd like to be able to extract data to another table based on a view. Then, at a later date, check to see if the first table or view has changed since the last extract. If not, then we don't need to re-extract the data, we know it hasn't changed.
I have been inserted one row in to table called as Teachers..
vTeachers is the View ctreated on Teachers Table.
When i Inserted one row in to Table Teachers i didn't see that row in vTeachers View.
How can i get that newly inserted values in to my View?
Hi,
I have a requirement to create #Temp table in database and insert values to it.
I use following code:
DbCommand dbCreateTable;
dbCreateTable = provider.CreateCommand();
dbCreateTable.Connection = conn;
dbCreateTable.CommandText ="Create table #MyTemp (Id varchar(10))";
dbCreateTable.ExecuteNonQuery();
string[] insertValues = {"Insert into #Mytemp values ('TestString1')",
"Insert into #Mytemp values ('TestString2')"};
DbCommand dbInsertData = provider.CreateCommand();
dbInsertData.Connection = conn;
foreach (String insertStr in insertValues)
{
dbInsertData.CommandText = insertStr;
dbInsertData.ExecuteNonQuery();
}
Code creates the Temp table but when it comes to insert statement, it throws error saying "Temp table not found".
Reason can be Create and Insert statement gets executed as 2 different sessions.
How to get the above requirement work fine?
Thank you.
HV
I would like to know about the DML process (Insert/update /delete) in a particular table .. it is like change tracking but I also want to know the modification date
I know CDC ( Change data capture ) but unfortunately it needs SQL 2008 developer/enterprise edition and my SQL server is SQL 2008 STANDARD edition.
I have multiple Common Table Expressions i.e CTE1,CTE2,...CTE5.
I want to Create Temperory Table and inserting data from CTE5. Is this possible to create?
like:
create table #temp (row_no int identity (1,1),time datetime, action varchar(5))
insert into #temp select * from cte5
Hi,
First time poster here, basically I have a second year university module on database design and for our coursework we have to model and create a database. One of the questions asks us to create a table that has a constraint on how many rows it can contain. I now that this is possible in some other databases, however I haven't seen a constraint that I could use on create table to limit the number of rows.. Does anyone now if this is possible?
Thanks,
Al
Hi All,
DECLARE @MyTableVar table( EmpID nvarchar(10) )
select login_id from cpm into @MyTableVar
Above syntax is not working....
Actually i want to table variable and store the result returned by stored procedure.
How to do that...
Thanks and reagards
A
I'm working on building my database tables, and I've ran into a bit of a situation.I have one table called 'Entities' that will hold information about people, places, and events. I would like to have the user be able to add other properties to these entities ad hoc.For example, a person might have a birthdate they want to record. So I was going to setup a DateProperties table that would have be setup something like this:DatePropertyIDEntityIDName (this would be 'Birthdate' in this situation)Value (the DateTime value)In that same way, I would also want users to be able to add something like Population to the places. So I was going to setup a NumberProperties table that would have something like this:DatePropertyIDEntityIDNameValueI noticed the structures for the table is basically the same; the only thing that is changing is the Data Type for the Value. I was thinking there has to be an easier/better way to do this sort of thing, perhaps all in a single table. I'm still trying to learn all the latest stuff from .NET 3.5. I was hoping to use LINQ to SQL, but I am willing to try anything.Any suggestions?
View 1 Replies View RelatedHello friendsI want to have a trigger for creating table on dialy basis.for e.g. I have a table say Data and on each day a new table is created like Data24Oct05 and so on. please help for writing the trigger for the same.thanks
View 4 Replies View RelatedHi!
After table creation I have got a message:
Warning: The table 'test' has been created but its maximum row size (11864) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes.
What does it mean?
Thank you,
Eve
Hi everyone and Happy Holidays!
I've got a problem with table creation in stored procedures (SQL Server 2000). We've got an application where the user login only has rights to execute stored procedures. The problem is that a stored proc is dynamically creating a table and so the owner of that table is being assigned to whatever login the application is using instead of dbo. It's causing numerous issues. Is there any way that this can be avoided or changed without granting the user sa privileges?
Thanks in advance,
Cat
I am starting to create a new database table based on an existing dbtable. My existing table has a list of ID's and a date range for each ID.
For example:
TableSource
ID Start DateTime End datetime
Y10012 01-12-12 13:00:00 01-19-12 13:00:00
So for this ID, I need my SQL statement to read this table, then create a new table and insert a new row for every second starting from the start date to the end date. I have several id's that span a week at a time. So I am expecting millions and millions of records once I am done.
End Result:
MainTable
IDFullTime
Y1001201-12-12 13:00:00
Y1001201-12-12 13:00:01
Y1001201-12-12 13:00:02
Y1001201-12-12 13:00:03
....
Y10012 01-12-19 13:00:00
Then once it completes reading S1001, it moves on to the next ID and appends it to the table. The date ranges are different for each ID- so it can't be hard coded.
Hi,
Can you create a table on one line and then update it on the next i.e. in the same query.
i.e.
create table test as select * from original
update test set test.a = .......
I know that they both work individually but whne i try to run them in one file i get "sql command not properly ended" - when i put a comma after 'original' i then get "invalid table name".
Am i just missing something
Cheers
CREATE DATABASE Dummy
ON
-- Primary file contains Startup information of the database
PRIMARY (
NAME = PrimaryLog,
FILENAME = 'D:primary.mdf',
SIZE = 5MB,
MAXSIZE = 500MB,
FILEGROWTH = 20MB ),
-- Holds The Data of LookUPTables,TPProfile,CRM.
(
NAME = Data,
FILENAME = 'D:Data.ndf',
SIZE = 5MB,
MAXSIZE = 500MB,
FILEGROWTH = 20MB ),
LOG ON
-- Stores The Log Information used To Recover The Database
(
NAME = Log,
FILENAME = 'D:Log.ldf',
SIZE = 5MB,
MAXSIZE = 500MB,
FILEGROWTH = 20MB )
Go
After this I want to create table on Data .
CREATE TABLE Sample (
No INT ,Name VARCHAR(30) , Department VARCHAR(4000) NULL
)
ON Data
GO
it shows invalid filegroup DATA specified
what may be wrong
Hi,
i'v send this email to understand how i can replicate a creation's table between two sql servers ?
the documentation of the replication explain the replication use the alter table !!
it's possible to detect with a query this creation of table and start after the replication ?
another solution ?
Regard's
CREATE DATABASE Dummy
ON
-- Primary file contains Startup information of the database
PRIMARY (
NAME = PrimaryLog,
FILENAME = 'D:primary.mdf',
SIZE = 5MB,
MAXSIZE = 500MB,
FILEGROWTH = 20MB ),
-- Holds The Data of LookUPTables,TPProfile,CRM.
(
NAME = Data,
FILENAME = 'D:Data.ndf',
SIZE = 5MB,
MAXSIZE = 500MB,
FILEGROWTH = 20MB ),
LOG ON
-- Stores The Log Information used To Recover The Database
(
NAME = Log,
FILENAME = 'D:Log.ldf',
SIZE = 5MB,
MAXSIZE = 500MB,
FILEGROWTH = 20MB )
Go
After this I want to create table on Data .
CREATE TABLE Sample (
No INT ,Name VARCHAR(30) , Department VARCHAR(4000) NULL
)
ON Data
GO
it shows invalid filegroup DATA specified
what may be wrong
I have a table I am trying to create and one of the columns needs to have a decimal that appears as 00.00 and does not round because the data example is 87.09, 86.50, 98.55 etc. I have tried every type of decimal formatting when creating I can find and nothing has worked. When I create the table and upload the data it keeps rounding like this: 87.00, 87.00, 99.00
The only way it does not round is if I do it as a char but that does not seem right when dealing with numerics.
hello alli want to create a phone table and it contains two fields empid ,ph.the phone table following format:Phone table------------------------------------------empid ph----- ---------------------------------office Mobile home--------- -------- --------100 9380768532 98455555 98822213--------------------------------------------i want above type of format and then how to insert into values thatphone table . please help me.
View 2 Replies View Related