Hello 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 RepliesI 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,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 RelatedHi 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 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
Suppose 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
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'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.
View 1 Replies View RelatedI 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 RelatedI want guidance for the following problem
I have to create one table and attach the trigger to the server given by the user. Assuming I have all sorts of permissions, how can I do this using CLR.
I tried this using data base project in c# and deploying it manually, it worked successfully but I am not able to get how can I provide this at run time to the user.
If I write one assembly on the client side for the same than how to run it on the back end (i.e., SQL Server).
Hi,
when I select sql server provider pointing to connection mydatabase for Logging the sysdtslog90 table is not getting created. However if I point the connection to msdb - sysdtslog90 it works fine.
Can I have sysdtslog90 under the application database? what are the steps to do this?
Thanks in advance.
Prabhakaran
I am using the below SQL to insert a table. The problem is after I run this, I run another script to populate the table (see below). The population script will work if I run it as INSERT INTO ... SELECT TOP 99.9999999 PERCENT ..., but if I put 100 PERCENT, or just use no percent limiter I get the following error: Msg 8624, Internal SQL Server error.
It is weird b/c once something is inserted in the table, i can run the populate script without any problems. Any idea as to why this is happening?
Thanks,
Dave
TABLE GENERATION SCRIPT
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tbCelebroAds]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tbCelebroAds]
GO
CREATE TABLE [dbo].[tbCelebroAds] (
[AdID] [int] IDENTITY (1, 1) NOT NULL ,
[BranchCode] [int] NOT NULL ,
[PropertyID] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[AdScheduleCode] [int] NOT NULL ,
[RecAtCentral] [int] NULL ,
[AdRan] [int] NOT NULL ,
[AdStatusID] [int] NOT NULL ,
[PatID] [int] NULL ,
[RunDate] [datetime] NOT NULL ,
[CreationCost] [float] NOT NULL ,
[BillCost] [float] NOT NULL ,
[AddedDate] [smalldatetime] NOT NULL
) ON [PRIMARY]
TABLE POPULATION SCRIPT
INSERT INTO tbCelebroAds (BranchCode, PropertyID, AdScheduleCode, PatID, AdStatusID, AdRan, RunDate, CreationCost, BillCost, AddedDate)
SELECT [TOP 100 PERCENT or no percent limiter doesn't work, TOP 99.9999999 PERCENT does] BranchCode, PropertyID, AdScheduleCode, cp.PatID, 6, 0, CAST(PubDate AS DATETIME), CAST(ISNULL(pat.Cost,0) AS DECIMAL(10,2)), 0, GetDate()
FROM tbCelebroView cv
LEFT JOIN tbCelebroPubs cp ON cv.PublicationName = cp.PublicationName AND cv.AdSectionName = cp.AdSectionName
LEFT JOIN tbPubToAdType pat ON cp.PatID = pat.PatID
WHERE CAST(BranchCode AS nvarchar(20)) + CAST(PropertyID AS varchar(20)) + CAST(AdScheduleCode AS nvarchar(20)) NOT IN
(SELECT CAST(BranchCode AS nvarchar(20)) + CAST(PropertyID AS varchar(20)) + CAST(AdScheduleCode AS nvarchar(20)) FROM tbCelebroAds)
I've done already the schemas for all the tables a you suggested.But when I created a new table the dbo prefix (dbo.ADM.NewTable) appears.I'd like this dbo not to automatically appear whenever i created a new table — as it requires me to alter whenever I created a new table.Is this something that is required to determine the creator of the table?Otherwise, I'd like that to remove.
View 7 Replies View RelatedI want to create a XML file with data in my table. I have a question about tags.
SELECT
-- Root element attributes
'http://tempuri.org/Form.xsd' AS 'xmlns',
'http://www.w3.org/2001/XMLSchema-instance' AS 'xmlns:xsd',
(
SELECT
-- Creating a default element
[Code] ....
This is my query. When I use 'xmlns' namespace the result is below:
<Form xmlns="http://tempuri.org/Form.xsd" xmlns:xsd="http://www.w3.org/2001/XMLSchema-instance">
<Veri xmlns="">
<Satir>
<Id>1</Id>
</Satir>
</Veri>
</Form>
I dont want to see 'xmlns' in 'Veri' tag. When I use xmlna or another word instead of 'xmlns' the result is below:
<Form xmlns="http://tempuri.org/Form.xsd" xmlna:xsd="http://www.w3.org/2001/XMLSchema-instance">
<Veri>
<Satir>
<Id>1</Id>
</Satir>
</Veri>
</Form>
How can I remove 'xmlns' from 'Veri' tag while using it in 'Form' tag
I need to programatically create a mdb file which will contain nullable columns. I am using C++ with ADOX for the table creation and ADO to perform the table update.
Although ADOX seems to create the table ok, Table->Columns->Appends does not set the fields as adColNullable as expected.
When I insert data using ADO::Recordset->AddNew the following error occurs :- "The field 'MyTable.Column 2' cannot contain a Null value because the Required property for this field is set to True. Enter a value in this field."
Am I on the right tracks here or do I need to adopt a different approach?
Code block to illustrate the problem :-
Code Block
ADOX::_CatalogPtr m_pCatalog;
ADOX::_TablePtr m_pTable;
ADOX::_ColumnPtr m_pCol1;
ADOX::_ColumnPtr m_pCol2;
ADODB::_ConnectionPtr m_pConn;
ADODB::_RecordsetPtr m_pRs;
//ADOX - Create Data Source
_bstr_t strcnn(("Provider='Microsoft.JET.OLEDB.4.0';Data source = C:\test.mdb"));
m_pCatalog.CreateInstance(__uuidof (ADOX::Catalog));
m_pCatalog->Create(strcnn);
m_pTable.CreateInstance(__uuidof(ADOX::Table));
m_pTable->PutName("MyTable");
m_pCol1.CreateInstance(__uuidof(ADOX::Column));
m_pCol1->Name = "Column 1";
m_pCol1->Type = ADOX::adVarWChar;
m_pCol1->DefinedSize = 24;
m_pCol1->Attributes = ADOX::adColNullable;
m_pTable->Columns->Append(m_pCol1->Name, ADOX::adVarWChar, 24);
m_pCol2.CreateInstance(__uuidof(ADOX::Column));
m_pCol2->Name = "Column 2";
m_pCol2->Type = ADOX::adVarWChar;
m_pCol2->DefinedSize = 24;
m_pCol2->Attributes = ADOX::adColNullable;
m_pTable->Columns->Append(m_pCol2->Name, ADOX::adVarWChar, 24);
m_pCatalog->Tables->Append(_variant_t((IDispatch *)m_pTable));
//ADO - Data Access
m_pConn.CreateInstance (__uuidof(ADODB::Connection));
m_pConn->Open(strcnn,_bstr_t(""),_bstr_t(""),ADODB::adOpenUnspecified);
m_pRs.CreateInstance(__uuidof(ADODB::Recordset));
m_pRs->Open("MyTable", _variant_t((IDispatch *)m_pConn,true),ADODB::adOpenKeyset, ADODB::adLockOptimistic, ADODB::adCmdTable);
// Define a SafeArray that contains field names.
SAFEARRAY * psaFields;
SAFEARRAYBOUND aDimFields[1];
aDimFields[0].lLbound = 0;
aDimFields[0].cElements = 1;
psaFields = SafeArrayCreate(VT_VARIANT, 1, aDimFields);
// Create a SafeArray for values.
SAFEARRAY * psaValues;
SAFEARRAYBOUND aDimValues[1];
aDimValues[0].lLbound = 0;
aDimValues[0].cElements = 1;
psaValues = SafeArrayCreate(VT_VARIANT, 1, aDimValues);
long ix[1];
_variant_t var;
//Insert Data
ix[0] = 0;
var = "Column 1";
SafeArrayPutElement(psaFields, ix, (void*) (VARIANT *) (&var));
ix[0] = 0;
var = "test data row 1 col 1 Only";
SafeArrayPutElement(psaValues, ix, (void*)(VARIANT *) &var);
_variant_t vtFields, vtValues;
vtFields.vt = VT_ARRAY | VT_VARIANT;
vtValues.vt = VT_ARRAY | VT_VARIANT;
vtFields.parray = psaFields;
vtValues.parray = psaValues;
m_pRs->AddNew(vtFields, vtValues); //!! Fails Here !!
m_pRs->Update();
m_pRs->Close();
m_pConn->Close();
What causes SQL Server 2000 to create tables with default column sizes of 8000 for a varchar datatype??
I would assume this can cause significant performance problems.
(see attached)
we planning to create partitioning on existing tables. The partitioning is on date column, there should be one partition for each year.
Creating of new partitions should be automated, and also we dont have any plans of archiving old data, all we want is that new partition creation should be automated.
As far as I know temp tables/objects will be created inside the default filegroup of the partially contained database and not in tempdb. Is it possible to either define a set of files dedicated to temp objects or define a second partially contained database dedicated to temp objects like tempdb?
View 0 Replies View Relatedis there a setting that will ebnable uniform extent allocation uponcreation of index/table by default ?if there isn't any default setting can you code it in?thanks,Doron
View 1 Replies View RelatedI am importing Differennt Excels Files into table. my require ment is after importing completed I need to insert all these Filenames ,File creation date into table. (for Auditing).
View 9 Replies View RelatedWe have an SSAS instance where when we run the query "select * from $system.discover_traces" the creation time in the resultset shows a different time from when we actually started the trace.
for example if we have create the trace at 3.30pm it shows 7.35 pm in the Sql server management studio resultset when we run the query "select * from $system.discover_traces".
I know this is not SQL forums but still if some body has solutions..then plz help
My backend is SQL.
My Ms Access database is always open and getting updated continously.
I need to create Job for SQL. This job will run every 10-15 min may be.
This job should pick latest data from MS Access and put in my SQL server database
Is it possible to create such kind of Job>
OR
Can my application read this MSAccess database when its open by some other application and getting updated continously?----I tried this in vain!!!!
Regards,
Mani
I have a task I need to create an SQL job, that will compare a file path listed in the database, and see if the path actually exists, and for the ones it does not see that dont exist to email the results to an operator ..
IS there a way? I need help.
Thanks
Hello to all!
I have a procedure "rebuild_index" and I would like to create a job running that procedure.
May someone send me a script (template) how to create this job, the most important:
When I click on job's properties->Steps->Edit->General->command I could change the database name, because I have a lot of servers and databases, so this way I could change only DB name in properties (not changing db name in job script)
Thank you very much for your help!
create proc p_rebuild_index as
declare @name varchar(100),
@string varchar(200)
declare c1 cursor for select name from sysobjects where type = 'U'
open c1
fetch c1 into @name
while @@fetch_status = 0
begin
set @string = 'dbcc dbreindex([' + rtrim(@name ) + '],"",85)'
execute (@string)
--print @string
fetch c1 into @name
end
close c1
deallocate c1
GO