in my database i have a "datecreated" feild, its datatype is "datetime", is it possible to set the defualt value as the time now? if so what do I put in the defualt value property? thanks a million si!
Hi Guys, Im new using SSIS and i just created a data flow that reads from multiple flat files (with a mutipleflatfiles conn manager) and inserts the files data to an sql server (with an ole db destination)
The thing is i need to insert the created date property of each file im importing and i dont know how to do this.
I've read i could do this with a script but im not very familiar to it.
Is there anyway to get the date an index was created on a table? I can see that there is a created date column on various system tables (sys.objects etc) but I can't find one related to an index.
Well you would think it was pretty simple. If I create a Database on Server a last week, take a backup of that database and put it on server b today, the CRDate in sysdatabases still shows the date of last week instead of today. I need to find the actual date for all 150+ servers from sql 7 - sql 2005 in our domain. Any ideas? I had heard that maybe SQL DMO had an object that might be helpfull but I have not been able to look it up anywhere or see how to use it using sp_oa procs.
I have a date parameter for a report that is intended to search 'within the last X week/month...'. The SQL for this is basically WHERE [datefield] >= @dateparam AND [datefield] <= GETDATE().
The @dateparam is defined in the report as 'From query', the value field is 'thedate', and the label field is 'thetext', and the dataset is:
Date Query dataset
SELECT GETDATE()-7 as thedate, '1 Week' as thetext, 1 as theorder UNION SELECT GETDATE()-14 as thedate,'2 Weeks' as thetext, 2 as theorder UNION SELECT GETDATE()-21 as thedate,'3 Weeks' as thetext, 3 as theorder UNION SELECT GETDATE()-31 as thedate,'1 Month' as thetext, 4 as theorder UNION SELECT GETDATE()-62 as thedate,'2 Months' as thetext, 5 as theorder UNION SELECT GETDATE()-(31*3) as thedate,'3 Months' as thetext, 6 as theorder UNION SELECT GETDATE()-(31*6) as thedate,'6 Months' as thetext, 7 as theorder UNION SELECT GETDATE()-365 as thedate,'1 Year' as thetext, 8 as theorder ORDER BY 3 When I go to run the report and selecting a value for the date parameter, there is never any output, and the data selection combo box just gets reset to show <select a value>.
I pages and use Ms sql Express and would like to have it condition-shape defual value to be the date, but if I choose the field have to be date-hour and then defual value chooses have nowly () to be it doesn't give myself any law, some that know what I do mistakes here?
I would like to know as to how can I put a Current Date & Time stamp on a FILE NAME automatically which is created by a DTS package.
E.g. I create a file named ABC.TXT daily. How do I get this file to have the current Date stamp so that the file name is ABC121201.TXT without any user intervention.
Don't know if this is the right place to ask the question, didn't know where to put it...
Strange thing is happening to me...I created different forms that all point (Record Source) on tables (one form - one table). Each table has some columns with Default or Binding Value set. I have a user that has Insert, Delete, Update and Select permissions.
When I insert a new record using the front end, some forms show the default values correctly, others don't show them at all (all the columns that should be populated with a default value are empty). If I add a View Definition permission to problematic tables --> surprise, surprise, default values get implemented...I'm confused...
Any ideas? Can a user w/ View Definition do some damage to the table?
I create a Dimension Date using SSAS 2008 but when i execute the dimension and i go to see the result i have this result:the result is not sorted..what i need is having the result order by year i mean i have Calendrier 2020,Calendrier 2019 ...
Any one please tell me is there any possible way to identify the table modified date.
I have checked the table created date from sysobjects or by right click properties. my requirement is to identify the exact date of table modification and column creation,alter dates. Is there any such provision in sql server 2000 or 2005 , My application is in sql server 2000.
I need to confirm this because some database structure modification has affected my application and causing dataloss i need to check with the date of structural change of table and lost data date can any one help
Hi, I know that there have been numerous posts on this topic, however none of the suggestions seems to resolve my problem. I am running a ASP 2.0 web application locally using SQL server 2005 express, but after using SQL Server Management Studio to browse the database of the application i get the following error everytime i log into the website: Cannot open user default database. Login failed. Login failed for user '<<machine name>>ASPNET' All i had done is attach then detatch the database in SSMS and it seems to have changed the access permissions somehow.I have ensured that the ASPNET user has full permissions (server roles and User mapping) in SSMS and i have granted the ASPNET user full rights to the ASPNETDB.MDF and log file, but i still get the same error. Its really urgent i sort this problem out as soon as possible, so if anyone has tackled this problem before please help. Ideally i don't want to have to reinstall sql server, but if that is the only option and it will definitely fix the problem then i will. Thanks, Jack
trying to get a new database created then running a script to created the tables, relationships, indexes and insert default data. All this I'm making happen during the installation of my Windows application. I'm installing SQL 2012 Express as a prerequisite of my application and then opening a connection to that installed SQL Server using Windows Authentication.
E.g.: Data Source=ComputerNameSQLEXPRESS;Initial Catalog=master;Integrated Security=SSPI; Then I run a query from my code to create the database eg: "CREATE DATABASE [MyDatabaseName]".
From this point I run a script using a Batch file containing "SQLCMD....... Myscriptname.sql". In my script I have my tables being created using "Use [MyDatabaseName] Go CREATE TABLE [dbo].[MyTableName] .....". So question is, should I have [dbo]. as part of my Create Table T-SQL commands? Can I remove "[dbo]."? Who would be the owner of the database? If I can remove the [dbo]., should I also remove dbo. from any query string from within my code?
SO when i try to load from Master table to parent and child table i am using using expresssion like
select B.ID,A.* FROM FLATFILE_INVENTORY AS A JOIN DMS_INVENTORY AS B ON A.ACDealerID=B.DMSDEALERID AND A.StockNumber=B.STOCKNUMBER AND A.InventoryDate=B.INVENTORYDATE AND A.VehicleVIN=B.VEHICLEVIN WHERE convert(date,A.[FtpDate]) = convert(date,GETDATE()) and convert(date,B.Ftpdate) = convert(date,getdate()) ;
If i use this Expression i am getting the current system date data's only from Master table to parent and child tables.
My Problem is If i do this in my local sserver using the above Expression if i loaded today date and if need to load yesterday date i can change my system date to yesterday date and i can run this Expression.so that yeserday date data alone will get loaded from Master to parent and child tables.
If i run this expression to remote server i cannot change the system date in server.
while using this Expression for current date its loads perfectly but when i try to load yesterday data it takes current date date only not the yesterday date data.
What is the Expression on which ever date i am trying load in the master table same date need to loaded in Parent and child table without changing the system Date.
Here i have small problem in transactions.I don't know how it is happaning. Up to my knowldge if you start a transaction in side the transaction if you have DML statements Those statements only will be effected by rollback or commit but in MS SQL SERVER 7.0 and 6.5 It is rolling back all the commands including DDL witch it shouldn't please let me know on that If any one can help this please tell me ...........Please............ For Example begin transaction t1 create table t1 drop table t2
then execute bellow statements select * from t1 this query gives you table with out data
select * from t2 you will recieve an error that there is no object
but if you rollback T1 willn't be there in the database
droped table t2 will come back please explain how it can happand.....................
HiI am using SQL 2005, VB 2005I am trying to insert a record using parameters using the following code as per MotLey suggestion and it works finestring insertSQL; insertSQL = "INSERT INTO Issue(ProjectID, TypeofEntryID, PriorityID ,Title, Area) VALUES (@ProjectID, @TypeofEntryID, @PriorityID ,@Title, @Area)"; cmdInsert SqlCommand; cmdInsert=new SqlCommand(insertSQL,conn); cmdInsert.Parameters.Add("@ProjectID",SqlDbType.Varchar).Value=ProjectID.Text; My query is how to detail with dates my previous code wasinsertSQL += "convert(datetime,'" + DateTime.Now.ToString("dd/MM/yy") + "',3), '";I tried the code below but the record doesn't save?string date = DateTime.Now.ToString("dd/MM/yy"); insertSQL = "INSERT INTO WorkFlow(IssueID, TaskID, TaskDone, Date ,StaffID) VALUES (@IDIssue, @IDTask, @TaskDone, convert(DateTime,@Date,3),@IDStaff)"; cmdInsert.Parameters.Add("IDIssue", SqlDbType.Int).Value = IDIssue.ToString();cmdInsert.Parameters.Add("IDTask",SqlDbType.Int).Value = IDTask.Text;cmdInsert.Parameters.Add("TaskDone",SqlDbType.VarChar).Value = TaskDoneTxtbox.Text;cmdInsert.Parameters.Add("Date",SqlDbType.DateTime).Value = date;cmdInsert.Parameters.Add("IDStaff",SqlDbType.Int).Value = IDStaff.Text;Could someone point to me in the right direction?Thanks in advance
I've created a job using SQL DMO (in VB6) but it fails to run. When try to manual start the job from the Enterprise Manager, it gives error "cannot start job because it does not have any job server(s) defined."
Here is the code :
Dim oSQLServer As New SQLDMO.SQLServer Dim oJobServer As New SQLDMO.JobServer Dim oJob As New SQLDMO.Job Dim oJobSch As New SQLDMO.JobSchedule Dim oJobStep As SQLDMO.JobStep
My goal is to select values from the same date range for a month on month view to compare values month over month. I've tried using the date trunc function but I'm not sure what the best way to attack this is. My thoughts are I need to somehow select first day of every month + interval 'x days' (but I don't know the syntax).In other words, I want to see
Select Jan 1- 23rd feb 1-23rd march 1-23rd april 1-23rd ,value from table
I'm buildng an application using the ADO.NET OleDB providor in order to maintain back end compatabiity with both SQL Server and Access Jet4.0. I need to get the Identity key of the new row created as a result of the following insert statement.
OLEDB COMMANT TEXT: "insert into tblPerson (LastName,FirstName,SSN,BirthDate,Family) values( ?, ?,?,?,?)";
I assume I need to add a select statement immediately following the insert. Can anyone help?
PS - not using stored procedures due to Access jet4.0 limitation
I want to remove 2 big ldf files (400 mgb and 300 mgb) and start db with only one ldf. I tried sp_attach_db specifing only mdf file but geting errors ------- microsoft help PRB: sp_attach_single_file_db Does Not Work for Databases with Multiple Log Files (Q271223) ..... You cannot attach a database that has been created with multiple log files without also attaching all the log files. ----
how can I remove those big ldf files and create new ldf (defult(1mgb)
This procedure runs and I see my GM names come up one at a time but I get no tables created.
Sorry about the sloppy code - I'm not a real pro.
declare @@GM Char(100) declare @SQL VarChar (2000) Declare spot cursor scroll for select GM from BIM_Historical_Performance.dbo.Performance_Master open spot fetch first from spot into @@GM While @@Fetch_Status =0 BEGIN
set @SQL = 'select Comp, Billto, Cust_name as Customer, Branch, BAC, [MgMt_Type], BondValue as Bondbucks , BondValueAlloc as Allocbucks, c1 as Curcode, u1 as CurUnderP$, s1 as Cur3mthBIMsales, p1 as performance, I1 as Inside, [IS_since] as Insidesince, O1 as Outside, [OS_since] as Outsidesince, c2 as CodeM-1, c3 as CodeM-2, c4 as CodeM-3, c5 as CodeM-4 ,c6 as CodeM-5, GM into ' +@@GM + ' from BIM_Historical_Performance.dbo.Performance_Master where BIM_Historical_Performance.dbo.Performance_Master.gm = ' +@@GM
I have created a installer for my application and database. When I run the installer it only creates the database but no tables created. But in some other workstation it works fine. I dont know what causes this problem....
Please guide me.
RON ________________________________________________________________________________________________ "I won't last a day without SQL"
ElementTypeDep_Cursor seems is not being created in this stored procedure:
Code Snippet ALTER procedure spCopyTemplateElementTypesToIssues @TemplateRecno integer, @ProjRecNo integer, @IssueRecNo integer as declare @ElementTypeRecno integer declare @ElementTypeDepRecno integer declare @ProjTypeRecno integer declare @PreElementRecNo integer declare @PostElementRecno integer declare @Count integer DECLARE element_Cursor CURSOR FOR SELECT ElementTypeRecNo FROM dbo.tblTemplateElementType where TemplateRecno = @TemplateRecNo OPEN element_cursor FETCH NEXT FROM Element_Cursor into @ElementTypeRecno --delete from tblElementCPO WHILE @@FETCH_STATUS = 0 BEGIN select @count = count (*) from tblElementCPO where ProjRecno = @ProjRecNo and IssueRecno = @IssueRecno and TemplateRecno = @TemplateRecno and ElementTypeRecno = @ElementTypeRecNo if @Count = 0 begin insert into tblElementCPO(Ignore, ElementTypeRecno, IssueRecno, ProjRecno, MaxAttemptNum, IsMileStone, ComponentOnly, Phase, TaskHoursEst, TemplateRecNo, ChangeDate, ChangePerson) values (0, @ElementTypeRecno, @IssueRecno, @ProjRecno, 5,0,6,99, 99,@TemplateRecno, getdate(), current_user) end FETCH NEXT FROM element_Cursor into @ElementTypeRecno END CLOSE element_Cursor DEALLOCATE element_Cursor select @Count = count (*) FROM dbo.tblElementTypeDep where TemplateRecno = @TemplateRecNo if @Count > 0 then begin DECLARE ElementTypeDep_Cursor CURSOR FOR SELECT ElementTypeDepRecNo, PreElementTypeRecNo, PostElementTypeRecno FROM dbo.tblElementTypeDep where TemplateRecno = @TemplateRecNo OPEN ElementTypeDep_cursor FETCH NEXT FROM ElementTypeDep_Cursor into @ElementTypeDepRecno, @PreElementRecNo, @PostElementRecno WHILE @@FETCH_STATUS = 0 BEGIN select @Count = count (*) from tblElementDepCPO where ElementTypeDepRecno = @ElementTypeDepRecno and PreElementRecNo = @PreElementRecNo and PostElementRecno = @PostElementRecno if @Count = 0 begin insert tblElementDepCPO (ElementTypeDepRecno, PreElementRecNo, PostElementRecno, ChangeDate, ChangePerson) values (@ElementTypeDepRecno, @PreElementRecNo, @PostElementRecno, getdate(), current_user) end FETCH NEXT FROM ElementTypeDep_Cursor into @ElementTypeDepRecno, @PreElementRecNo, @PostElementRecno END CLOSE elementTypeDep_Cursor DEALLOCATE elementTypeDep_Cursor end
called by Code Snippet Dim cnQI02414 As New SqlConnection(My.Settings.csQI02414Dev) Dim cmd As New SqlCommand Dim reader As SqlDataReader cmd.CommandText = "spCopyTemplateElementTypesToIssues" cmd.CommandType = CommandType.StoredProcedure Dim spmTemplateRecNo As SqlParameter = _ cmd.Parameters.Add("@TemplateRecNo", SqlDbType.Int) spmTemplateRecNo.Value = _ Me.cbTemplate.SelectedValue Dim spmProjRecNo As SqlParameter = _ cmd.Parameters.Add("@ProjRecNo", SqlDbType.Int)
spmProjRecNo.Value = _ Me.cbProject.SelectedValue
Dim spmIssueRecNo As SqlParameter = _ cmd.Parameters.Add("@IssueRecNo", SqlDbType.Int)
spmIssueRecNo.Value = _ Me.cbIssue.SelectedValue
cmd.Connection = cnQI02414 cnQI02414.Open() reader = cmd.ExecuteReader() ' Data is accessible through the DataReader object here. cnQI02414.Close()