 |
|
This is not tested and it is not the only way to do this:
UPDATE MYTABLE
SET DOB = CAST(CAST(YEAR(DOB) AS VARCHAR(4)) +
CAST(DAY(DOB) AS VARCHAR(2)) +
CAST(MONTH(DOB)AS VARCHAR(2)) AS DATETIME) +
CAST(DOB AS TIME)
|
|
|
|
|
Thank you.
I got an error when trying your code:
"Type TIME is not a defined system type"
I am using Microsoft SQL Server
|
|
|
|
|
I use TIME with SQL2008. You can use the CONVERT command to give you the time portion of the DATETIME column as a VARCHAR. (Look at 108)
|
|
|
|
|
taibc wrote: Do you know how can do that ?
Using string-manipulation functions. The problem here is not the format of your date, but the fact that it's stored as a string, not as a date.
What are you going to do with the date "6 may"? Would that become "5 juli", or are you going to "hope" that it is the correct date (that's what you state; you'd like to skip those)?
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
|
|
|
|
|
Thank you.
Yes, my expected result is 5 June.
I am not good with fuctions in sql, do you have any ideas ?
Kind regards,
|
|
|
|
|
taibc wrote: Yes, my expected result is 5 June.
That's not what you stated in your original question; take a look, you wanted only those where you could "invalidate" the date by having a "day" larger then the number of months. 5 may would be skipped if you do it like that.
taibc wrote: I am not good with fuctions in sql, do you have any ideas ?
Convert it to a date-column, as it should have been. It's either that, or trying your luck at string-manipulation. You'll have to write the code yourself btw, I'm not going to.
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
Hello to all,
I'm new to this forum and a few days ago I started to learn web design.
I have a porjet to start in September and I am looking to make effective technical.
I have to send daily sms to customers and I diffenrents around 25,000 customers each with 25,000 different phone numbers and different addresses.
My socui is to do all this from a website.
I want to send emails to smss and internet party.
I will create a database and sales agents will charge to send the data by group. Chauqe day I have to meet about 20 000 people.
So my problem is what solution I need? I mean how to make it be effective?
- I want my system to be independent, I do not want to depend on a GSM operator here, it will not be possible given the daily flow and no operator still offers unlimited sms offers here (I am in West Africa ).
- I would like for example to specify the sender according to the groups of my clients.
I am ready to buy equipment, I made a few searches on the net and I saw devices like sms gateway but I do not know how these devices work.
Is it possible to do this with php? and be totally independent?
Is there a solution where I can use the services of an operator in Europe or Canada or state States?
I want my system to be truly independent as far as possible.
What equipment do I need?
How to proceed?
I hope I have your good detailed answers.
|
|
|
|
|
Hi,
I have an SSIS package which does some Database functionalities, I want to deploy that package as just .dtsx file on to some servers filesystem and call it from a schduled task.
Can I just copy the .dtsx file from the solution folder and put it on some server and execute it from .Net code.
If not how to deploy an SSIS package on to filesystem as just .dtsx file and call it from .net code to finish its functionalities.
I am also searching on the internet but any kind help like code snippet, suggestion or any link would be appreciated.
Thanks in advance.
Thanks & Regards,
Abdul Aleem Mohammad
St Louis MO - USA
|
|
|
|
|
|
What is it with you people today, the previous bloke wanted to use a trigger, you want to deploy a dtx and schedule it with some tool. USE A SQL JOB. That is precisely what it is designed for! there are a whole bunch of stuff available [^]
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hi. I am creating an application that uses sqlserver and asp.net c#. I need to create a trigger that inserts data into a table 30 days before an expiry date field in another table. I have created the trigger but dont know how to implement the dates? Any suggestions would be appreciated.
|
|
|
|
|
Does the date go in when the "expiry date" column is populate or do you want it to go in 30 days before the date?
|
|
|
|
|
What you are describing is not a trigger spit but a JOB, the job would call a proc that checks for expiry dates 30 days away and insert the appropriate record. You would schedule the JOB to run daily.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
thanks for the reply. I had a feeling a trigger would be difficult or impossible to implement in this way.. Not sure how to create this job tho. Any pointers would be appreciated?
|
|
|
|
|
I already have a table with an appointment date entered. I need to update another table 30 days before before this appointment date. Thanks
|
|
|
|
|
Create a stored procedure that does the following
Get the expiring records
insert the relevant data into the target table.
In SSMS create a Job that calls the stored proc
schedule the job to run every day
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
thanks I'll give it a try!
|
|
|
|
|
I am new to ssis. I Created on pakcage which loops excel fils(.xls) of one folder and extracts to database.
I placed Excel files in one folder (like C:\Documents and Settings\username\My Documents\Temp). The Sqlserver2005 server located on another machine (where i need to dump the data from excel to this destination server.)
I am running package on my local machine. When i run it is giving below error at Excel Source of Data Flow task.
SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Excel" failed with error code 0xC0202009.
Please help me out.
Thanks,
Ravikiran
|
|
|
|
|
My usual attempt to resolve a sql error is to drop the error message into google[^] - seems like it is a common problem.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hi All,
In DB2, i have used XMLAGG built in function in order to convert Rows to Column String Concatenation. Since lot of records are available in the table, performance wise it was too slow. Whether any user defined function is available for DB2 for string concatenation.
Thanks in Advance.
|
|
|
|
|
EmpID Leave_Year Taken Credit Balance
435 2014 0 22 22
435 2013 10 22 12
435 2012 24 22 0
435 2011 20 22 2
435 2010 23 22 0
435 2009 21 22 1
I want to Update this table Balance column value with the Next row Balance column value
eg: Leave_Year 2011, Balance value (2) Update with Leave_Year 2012 Balance Value (0)
0 - 2 = -2 should get in 2011 Balance column, use with this where condition, where Next row (2012) Taken>0
How to write Update sql for this
|
|
|
|
|
Can you explain more in details your case?
I Love T-SQL
"VB.NET is developed with C#.NET"
If my post helps you kindly save my time by voting my post.
|
|
|
|
|
Hi,
Check the Script
SELECT M.ID,(ROW_NUMBER() OVER(PARTITION BY M.EmpID ORDER BY M.EmpID)) 'RowNumber', M.EmpID, M.Leave_Year, M.Taken, M.Credit,
(SELECT SUM((ISNULL(Balance,0.0))
FROM Table_Name WHERE ID<=M.ID AND EmpID=M.EmpID)
FROM Table_Name M
ORDER BY M.EmpID
|
|
|
|
|
I wouldn't do that. It's against the "Rules" of normalization. You don't want to make a row dependant on another row in the same table.
Think of all the fuzz to update the table when you find out someone inserted some wrong data last year, or when HR decides to change the rules (quite probable actually).
So you should make a query that gets you the result you want instead.
Hint, checkout ROLLUP.
For example:
SELECT EmpID,
CASE WHEN (GROUPING(Leave_Year) = 1) THEN 'Total Balance'
ELSE ISNULL(Leave_Year, 'UNKNOWN')
END AS Leave_Year,
SUM(Balance) AS Balance
FROM MyTable
GROUP BY EmpID, Leave_Year WITH ROLLUP
I haven't tested this code, but it should give you an idea.
I also assumed SQLServer, Different Databases have different syntax. Use Google.
Be excellent to each other. And... PARTY ON, DUDES!
Abraham Lincoln
|
|
|
|
|
Several screens (or one very functional one) to view the data in a variety of ways like by Phase, by ESXHost, by Application.
I think if we have several drop downs for filtering then that would work; so I can choose Phase 1, to show only the databases that will be migrated in Phase 1, then I can choose some other criteria and the screen shows a subset of data etc.
3. The Pre-requisites and Post-Migration checks should show whether the Phase or database migration is ready to start or is ok to complete.
|
|
|
|
|
Dear all,
I require your opinion on what approach is good and/or practical for the following scenario.
There is a web application which queries data from SQL server.
For example:
Users report to a manager
1. The process by clicking a button is finding who reports to me (manager)
2. So there is a stored procedure written to find the users reporting to the manager
Now, there is a report (rdlc on the web server displayed via reportviewer control) which takes the manager as an input to generate report data for the staff members reporting to that manager.
The stored procedure for this report uses existing stored procedure (as in step 2 above) to find the staff members reporting to the manager and then generate required data.
My question is:
1. Is this the right way of doing this
2. Is there a need to develop a separate SQL view just for this report
3. What would be the best approach on doing this.
Please help.
Thanks
Regards,
Nayan
|
|
|
|
|
I don't see any issues with this approach, the dedicated view may be useful if reconciliation is required or you are going to use an OLAP system but not for you current requirements.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Thank you very much.
I just needed a clarification for not under-estimating my decision over my superior.
Thank you very much.
|
|
|
|
|
Hi all. I have run into a problem with inserting values into table.
I have 2 tables: PUBLISHER with fields: PUBLISHER_CODE (Primary Key) and PUBLISHER_NAME. BOOK table fields: PUBLISHER_CODE (Primary Key and Foreign key to PUBLISHER table) and TITLE.
The user enters bookCode and publisherName into 2 textboxes
Here is the code for inserting:
"INSERT INTO BOOK (TITLE) VALUES(@bookCode)";
"INSERT INTO PUBLISHER (PUBLISHER_NAME) VALUES(@publisherName)";
When running programme, the PUBLISHER table generates a new PUBLISHER_CODE (Primary Key) and publisherName inserted.
Now problem is for the BOOK table the following error appears:
"Cannot insert the value NULL into column 'PUBLISHER_CODE'", table BOOK 
Why is this happening? Doesnt BOOK generate same/new PUBLISHER_CODE from PUBLISHER table since its a foreign key?
|
|
|
|
|
Member 9912091 wrote: Doesnt BOOK generate same/new PUBLISHER_CODE from PUBLISHER table since its a foreign key?
No - you have to retrieve it from the inserted publisher record. I use a stored proc for this and return the new record after it is inserted, then you can use that ID to insert the book record.
Or
Wrap the whole thing in a SQL Transaction in a stored proc and do the insert to both table at once.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Primary key value cant be null. You have to provide any value for that.
|
|
|
|
|
Bikash Prakash Dash wrote: You have to provide any value for that
No you have to provide the correct key, any key will corrupt your data.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hi all. I am busy writing a program, in Microsoft Visual Web Developer, and have a database was several tables. The one I am focusing on now is the ACCOUNT table. This table contains username, password and email column.
I want to compare a username entered into a textbox with the existing usernames in the ACCOUNT table, so that I can create a message saying that the entered user already exists.
I know how to do this in C# with if statements, but am really unsure with SQL, since not sure how to use it with SELECT.Thanks
|
|
|
|
|
Member 9912091 wrote: I want to compare a username entered into a textbox with the existing usernames in the ACCOUNT table
SELECT 1
FROM Account
WHERE Username = @Username Returns 1 of the username exists 
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
Thanks!
|
|
|
|
|
Yeah, what Eddy said. But, provided you have a unique index on the column (and you should), it may be more efficient simply to try the INSERT and catch the exception -- you have to do that anyway.
modified 11 May '13 - 13:36.
|
|
|
|
|
Instead of using 1 in Eddys query use count(*), this will tell you how many records match the name.
You should study database design as you may run into some of the basic errors made by newbies. Primary keys and unique constraints.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hi All,
If any one tell me by which i can transfer table from one sql server database to another sql server database.
|
|
|
|
|
|
Hi all. I am using Microsoft SQL Server 2008 Management Studio. I have been trying to create a database (containing tables etc) from a query file (Delivery.sql).
In the Object explorer I right-clicked Database folder and created the "Delivery database". I then clicked New Query and pasted the contents of Delivery.sql (from notepad) into the new query.
Now when I execute this code, it says it has completed successfully, but in the Object Explorer the "Delivery database" does not contain any tables . I am confused and am in need of help . Thanks.
Here is the image of the object explorer: http://imageshack.us/a/img694/7558/sqlobjectexplorerp.png[^]
Here is the code of the query file (Delivery.sql):
USE [Delivery database]
GO
CREATE DATABASE [Delivery] ON PRIMARY
( NAME = N'Delivery', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\Delivery.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N'Delivery_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\Delivery_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO
ALTER DATABASE [Delivery] SET COMPATIBILITY_LEVEL = 100
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [Delivery database].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO
ALTER DATABASE [Delivery] SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE [Delivery] SET ANSI_NULLS OFF
GO
ALTER DATABASE [Delivery] SET ANSI_PADDING OFF
GO
ALTER DATABASE [Delivery] SET ANSI_WARNINGS OFF
GO
ALTER DATABASE [Delivery] SET ARITHABORT OFF
GO
ALTER DATABASE [Delivery] SET AUTO_CLOSE OFF
GO
ALTER DATABASE [Delivery] SET AUTO_CREATE_STATISTICS ON
GO
ALTER DATABASE [Delivery] SET AUTO_SHRINK OFF
GO
ALTER DATABASE [Delivery] SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE [Delivery] SET CURSOR_CLOSE_ON_COMMIT OFF
GO
ALTER DATABASE [Delivery] SET CURSOR_DEFAULT GLOBAL
GO
ALTER DATABASE [Delivery] SET CONCAT_NULL_YIELDS_NULL OFF
GO
ALTER DATABASE [Delivery] SET NUMERIC_ROUNDABORT OFF
GO
ALTER DATABASE [Delivery] SET QUOTED_IDENTIFIER OFF
GO
ALTER DATABASE [Delivery] SET RECURSIVE_TRIGGERS OFF
GO
ALTER DATABASE [Delivery] SET DISABLE_BROKER
GO
ALTER DATABASE [Delivery] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO
ALTER DATABASE [Delivery] SET DATE_CORRELATION_OPTIMIZATION OFF
GO
ALTER DATABASE [Delivery] SET TRUSTWORTHY OFF
GO
ALTER DATABASE [Delivery] SET ALLOW_SNAPSHOT_ISOLATION OFF
GO
ALTER DATABASE [Delivery] SET PARAMETERIZATION SIMPLE
GO
ALTER DATABASE [Delivery] SET READ_COMMITTED_SNAPSHOT OFF
GO
ALTER DATABASE [Delivery] SET HONOR_BROKER_PRIORITY OFF
GO
ALTER DATABASE [Delivery] SET READ_WRITE
GO
ALTER DATABASE [Delivery] SET RECOVERY SIMPLE
GO
ALTER DATABASE [Delivery] SET MULTI_USER
GO
ALTER DATABASE [Delivery] SET PAGE_VERIFY CHECKSUM
GO
ALTER DATABASE [Delivery] SET DB_CHAINING OFF
GO
|
|
|
|
|
Did you refresh the Object Explorer after creation?
|
|
|
|
|
Yeah it didnt work. However I realised that this script didnt have any code to create tables so I used a different script with "CREATE TABLE" code in the query and it worked! thanks
|
|
|
|
|
Hi there, I just want to get some opinions on which tool to use to diagram an ER Picture of a DB model having approx 900 tables, but ....
I have tried a few (Erwin, Aqua Data Studio, Powerdesigner and a few more - even tried Visio). All have problems because I just have the tables - no modelled relations (a pretty bad model but that's the fact). A few PK's almost no FK's. All this is in the code layer or even in the head of the developer only. I want to put all this together to graficaly illustrate our data structure.
What I need to get this job done is
- reverse engineer to have a starting Point (Oracle 11 and MSSQL2008) to have all tables in this tool (on a hidden layer if possible)
- Select some tables and copy them (link) to a new layer to switch visibility depending on the development task that i want to give to a developer
- Display compact (square with table Name, and PKs only). Hide all other columns
- a good way to arrange everything on Screen to avoid crossing Relations and to nicely arrange every table and relation
- toggle visibility of a layer
- a certain table appears on more than one layer depending on the development tasks
- something that makes it all "nice" to present to the Boss.
- crow feet Notation
I more search a documentation type of drawing tool rather than a physical modelling. Some of the tested tools started with creating "alter table add column" scripts that immediately change my DB. This is something I dont need.
Any idea out there?
Any tools avail that accomplish this task?
Thanks a lot in advance
Ole
|
|
|
|
|
I'm using Oracle SQL Developer Data Modeler[^]
ole.Grossklaus@gmx.de wrote: - reverse engineer to have a starting Point (Oracle 11 and MSSQL2008) to have all tables in this tool (on a hidden layer if possible
Supports Oracle (duh), MSSQL and DB2.
ole.Grossklaus@gmx.de wrote: - Select some tables and copy them (link) to a new layer to switch visibility depending on the development task that i want to give to a developer
It's tab based, you can create a new view with a subset of existing tables.
ole.Grossklaus@gmx.de wrote: - Display compact (square with table Name, and PKs only). Hide all other columns
Yes
ole.Grossklaus@gmx.de wrote: - a good way to arrange everything on Screen to avoid crossing Relations and to nicely arrange every table and relation
Auto arrange sucks big donkey balls. But it does in all tools in my opinion. So it's up to you to do the work I'm afraid.
ole.Grossklaus@gmx.de wrote: - toggle visibility of a layer
It's tab based, and you can have one or more windows and move the tabs between them.
ole.Grossklaus@gmx.de wrote: - a certain table appears on more than one layer depending on the development tasks
A table can appear in many windows/views, not sure if this is what you mean.
ole.Grossklaus@gmx.de wrote: - something that makes it all "nice" to present to the Boss.
Use Powerpoint.
ole.Grossklaus@gmx.de wrote: - crow feet Notation
Barker notation - check
Be excellent to each other. And... PARTY ON, DUDES!
Abraham Lincoln
|
|
|
|
|
Microsoft SQL Server 2012 introduces 14 new built-in functions. These new functions are:
Conversion functions
> PARSE
> TRY_CONVERT
> TRY_PARSE
Date and time functions
> DATEFROMPARTS
> DATETIME2FROMPARTS
> DATETIMEFROMPARTS
>DATETIMEOFFSETFROMPARTS
> EOMONTH
> SMALLDATETIMEFROMPARTS
> TIMEFROMPARTS
Logical functions
> CHOOSE
> IIF
String functions
> CONCAT
> FORMAT
|
|
|
|
|
|
Karthik_J, Coimbatore wrote: Microsoft SQL Server 2012 introduces 14 new built-in functions.
And you posted this to demonstrate that you can read documentation maybe?
|
|
|
|
|
I am using an Access 2000 database file. When parsing user input that has SQL reserved characters, I put [] around any reserved characters I find. This works fine, except when dealing with %s.
I have a table named "Foo", with two rows:
Type as text, SubType as text, with two rows of values:
"Sales Tax (%)", "NY"
"Sales Tax (%)", PA"
When I execute the following:
SELECT * From Foo WHERE Type LIKE '%Sales Tax ([%])%'
I get the rows returned I expect, where the row has a value in the Type column of "Sales Tax (%)"
But when I execute:
SELECT * From Foo WHERE Type = 'Sales Tax ([%])'
No rows are returned
Even if I omit the parenthesis around the % sign, it does not find the rows.
Is there something odd I am doing wrong, perhaps specific to Access SQL?
|
|
|
|
|
<edit>move on, nothing to see here except the proofs of to little sleep.</edit>
Access isn't SQLServer. You're not supposed to use brackets either.
I would strongly recommend renaming the offending fields.
More info here[^].
I especially like this part: "However, if you do use the special characters, you may experience unexpected errors."
Be excellent to each other. And... PARTY ON, DUDES!
Abraham Lincoln
modified 10 May '13 - 10:49.
|
|
|
|
|
I am not sure I understand. The % character does not appear in any field names, or table names. The data type of both fields, named "Type" and "SubType" are text, and that text data can contain a % character as part of whatever text the user chooses to enter into the field, along with other special characters.
(I have an application where I prompt the user to enter a text value for the Type and SubType fields, and I save that text into those fields as a new row. The problem I have is that the user can enter a % as part of their user-entered text, and I need to be able to search on text field data for a % character they may have entered; I can put brackets around all other special characters when building my query and they are found fine, but the % just doesn't work for some reason when used in an = query.
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Rant 
Admin 
Submit an article or tip
Post your Blog
