|
Hi All,
I have a stored procedure as below,
ALTER PROCEDURE [dbo].[usp_Update_Service_Program_Detail]
@PK_Service_Program_County_RateCap_Detail int,
@Unit_Type int,
@Rate_Cap decimal(15,2),
----@County int,
@ModifiedBy nvarchar(50),<br />
@ModifiedOn datetime=getdate
AS
BEGIN
select '220' 'Col' -- Test statement
--Logic is here
END
But when I am running the stored procedure as below:
exec usp_Update_Service_Program_Detail @PK_Service_Program_County_RateCap_Detail= 53196434
, @Unit_Type=1
, @Rate_Cap=100
, @ModifiedBy='aaleemmo'
, @ModifiedOn=getdate
I am getting the following error:
Error converting data type nvarchar to datetime at @ModifiedOn=getdate
Why is it an error, when getdate returns datetime why should I convert it into Datetime again, when I try it, its giving me error. Can anybody please help me what is the way to execute it and I am getting similar problem from my C# code also, can anybody please help me in this regards.
Thanks in advance.
Thanks,
Abdul Aleem
"There is already enough hatred in the world lets spread love, compassion and affection."
|
|
|
|
|
indian143 wrote: @ModifiedOn datetime=getdate
Try GetDate()
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Dear Abdul Aleem.
i find your error and please try this code.
create PROCEDURE [dbo].[usp_Update_Service_Program_Detail]
@PK_Service_Program_County_RateCap_Detail int,
@Unit_Type int,
@Rate_Cap decimal(15,2),
----@County int,
@ModifiedBy nvarchar(50),
@ModifiedOn datetime=getdate
AS
BEGIN
select '220' 'Col' -- Test statement
--Logic is here
END
exec usp_Update_Service_Program_Detail @PK_Service_Program_County_RateCap_Detail= 53196434, @Unit_Type=1
, @Rate_Cap=100
, @ModifiedBy='aaleemmo'
, @ModifiedOn=''
|
|
|
|
|
Hi,
I have been given an already existing application to modify, there is a need to modify an existing Table in the Database also, so I added a new Table which has association of the old tables to create a many to many relationship with unique combination. Like for example if Unit Cost of a Service and Program was same for all Cities earlier now I have added a Table which has ServiceProgramCostId, CityId and Unit Cost, which is ServiceProgramCityCostId as PK.
Now the problem came with communication, I am trying to explain my manager that this same City impact will have in other tables as well, its better to create a combination for LineItemsServiceProgramCityCost instead of LineItemsServiceProgramCost and BusinessRulesServiceProgramCityCost instead of BusinessRulesServiceProgramCost as well, as they are now dependent upon the City.
The other thing is the Previous Developer was deleting the orphaned records from ServiceProgramCost table so with new Combination ServiceProgramCityCost I am not able to delete the records from LineItemsServiceProgramCost, BusinessRulesServiceProgramCost tables like earlier, because the deletion now means from BusinessRulesServiceProgramCityCost table, I am becoming nervous about deleting records from LineItemsServiceProgramCost, BusinessRulesServiceProgramCost tables.
My question is, is it not better to use the new city relationship in those two tables is better or is it better to go ahead with old approach but limiting the deletes? The previous developer who worked is just saying simple what's there? But not suggesting me or maybe saying with Manager that I am not able to work or something. Our Business Analyst is on maternity leave.
Any idea would be greatly helpful - thanks in advance.
Thanks,
Abdul Aleem
"There is already enough hatred in the world lets spread love, compassion and affection."
|
|
|
|
|
Hello,
I am working on a presentation on how developers could improve their relationship with their dbas. I am trying to receive as much feedback as possible. What is the biggest pet peeve(s) DBAs have with developers?
|
|
|
|
|
And you are asking this in a developer forum!
You might be better served trying SQL Server Central and the Oracle forums, you are going to get a higher DBA content there.
Personally I think we (developers) need to understand the restrictions under which a good DBA works.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
BBQ.
Not enough of it.
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
|
|
|
|
|
Member 13200024 wrote: What is the biggest pet peeve(s) DBAs have with developers?
I don't know but I have never understood why there are problems. It really boils down to egos. A lot of people in this industry have too big of an ego and can't work well with other people.
I've been lucky enough to never work for such a big company that there were different roles like that. I've been lucky enough to always be a dba and a developer.
I have heard stories where developers do not have enough access to the DB so it makes it hard to do their job when they are always waiting on some dba to write code for them. But I'm not sure what complaints the dbas have about devs.
Everyone is born right handed. Only the strongest overcome it.
Fight for left-handed rights and hand equality.
|
|
|
|
|
This is a little late, but here go some of my annoyances.
1) The sky is falling emails. Just because your application isn't working, doesn't mean the entire environment is down.
2) CCing everyone in management because you are having an issue...
3) Failure to plan, just because you failed to plan out a release doesn't constitute an emergency on my part.
Not communicating releases and complaining when something doesn't work right.
[The DW team, loves to chew up 100s of gigs of space in a single release without communicating then screaming about when the drives fill up]
4) Trying to use the Database server as a File server, send mail server, or other random stuff. No, the database server is there to serve up data, not be some Swiss utility knife.
5) Not keeping in mind my time, and the totality of the environment. Please start out with where your problem is at.
I have 1700 databases spread across Dev, Test, PreProd and Production. No I don't just know where you are having an issue at, if I was psychic I would come up with the winning lotto numbers so I no longer need to work.
6) Security rules are not just setup randomly, they are often established by controls put in place by auditors.
I am accountable to those rules, so everyone needs to follow them.
Things like demanding SA rights to a db instance, no, there is nothing at the Database Server level you need to change. Things have been setup that way for a reason.
7) Not taking advice on a designs that are just terrible.
Just because it worked with 10 rows of data doesn't mean it will scale with 10M rows. Complaining about the server isn't going to solve your issue.
Setting aside the petty grips, a lot of the issues boil down to communication and a willingness to collaborate. My time gets stretched in a lot of directions, I go from meetings with network, server, dev, auditors, management, vendors and yes even end users. It’s not always possible for me to just drop everything to help someone out.
Common sense is admitting there is cause and effect and that you can exert some control over what you understand.
|
|
|
|
|
One computer can install multiple versions of Microsoft Visual Studio 2005, 2008, 2010, ... so SQL Server Management Studio can install multiple versions of 2005, 2008, ... the same computer ?
|
|
|
|
|
Yes
=========================================================
I'm an optoholic - my glass is always half full of vodka.
=========================================================
|
|
|
|
|
You can do it, but since the latest is able to manage any database, I would not recommend installing several versions. Just install the latest one and you will be able to manage any MS SQL database.
"I'm neither for nor against, on the contrary." John Middle
|
|
|
|
|
Doesn't the management studio also allow you to design solutions? Is that also backwards compatible in a way that one would feel comfortable deploying it?
|
|
|
|
|
SSMS will adapt itself to the version of the database it is connected to. Thus, you will not be able to setup something that would not be compatible with the database version you are working on.
I've been using this software for years now, and never had to worry installing several versions on a single computer.
That being said, you can easily test that latest version allows you to work on every database.
"I'm neither for nor against, on the contrary." John Middle
|
|
|
|
|
Yes, but I see no real need for multiple versions of SSMS in itself.
That said, I still have the predecessors (SQL Enterprise Manager & SQL Query Analyzer, from SQL 2000) and its companion installed; I feel that Query Analyzer is a much lighter weight component for running queries
I am open to new ideas, though; as I have been using the in-development SQL Operations Studio which is a little heavier than SQL-QA, but not as bloated as SSMS
Director of Transmogrification Services
Shinobi of Query Language
Master of Yoda Conditional
|
|
|
|
|
Assuming I have file data northwnd.mdf version 2005 I want to convert to file data version 2008 or reverse ?
|
|
|
|
|
Use backup and restore going from 2005->2008. You cannot reverse the process using this method.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
As Mycroft said, you can upgrade, but not downgrade. Restore the older version database to a newer version instance, or detach from the old version and attach to the new version.
Be aware that backup formats changed between SQL2008 and SQL2008 R2, which can cause incompatibilities
=========================================================
I'm an optoholic - my glass is always half full of vodka.
=========================================================
|
|
|
|
|
You could export it - dump it as sql statements rather than as a back up.
If the exported sql was fairly simple then it would work in either database. It is possible that it would require some modification to go from 2008 to 2005.
|
|
|
|
|
I am using Sql Server 2017 with Transact and a file with synonyms in FTData. I have found several serious flaws and problems but in the Microsoft MSDN forum and in Microsoft connect no one answers me. The rest of the people say they do not know how it works.
Video: https://youtu.be/Il7-DDKfHiU
Problem 1: Freetexttable Error With More of 3 Words.
I use Freetexttable with thousands of synonyms in a simple query in two Pc. By entering up to 3 words "Microsoft Sql Management Studio" instantly returns the result and the memory remains stable in 6Gb.
If I enter 4 words he is thinking for 10 minutes and the memory consumes until 12Gb. At the start of the video you can see what it takes Sql Server to load the synonyms and every few minutes if Sql Server is not used again it loads them again.
Example:
SELECT [Table].*, FT.* FROM [Table] INNER JOIN FREETEXTTABLE([Table], [Contenido], 'Word1 Word2 Word3') FT ON [Table].Id=FT.[Key] WHERE ([Table].STATE IS NULL OR [Table].STATE = ' ') ORDER BY RANK DESC, Page
Problem 2: Transact does not return the inflections of a word if you do not accent it correctly.
When looking for the inflectional of a word, if I introduce the word with the accent the inflections appear well. The server collation is SQL_Latin1_General_CP1_CI_AI
If I introduce the word without the accent, the inflections do not appear. In Spanish café and cafés are accentuated.
Video: https://youtu.be/4DX-Y3eoDo4
SELECT display_term FROM SYS.DM_FTS_PARSER('FORMSOF(INFLECTIONAL, café)', 3082, 0, 0)
display_term returns: cafes cafe
SELECT display_term FROM SYS.DM_FTS_PARSER('FORMSOF(INFLECTIONAL, cafe)', 3082, 0, 0)
display_term returns: cafe
When doing the same with the synonyms, returns the same results with or without accent.
SELECT display_term FROM SYS.DM_FTS_PARSER('FORMSOF(THESAURUS, cafe)', 3082, 0, 0)
SELECT display_term FROM SYS.DM_FTS_PARSER('FORMSOF(THESAURUS, café)', 3082, 0, 0)
display_term returns: cafe chicoria sucedaneo mezcla chicoria. In Spanish sucedáneo are accentuated.
Note:After making the video I tried to recover the inflections of the word echaré (with and without accent) and returns the inflections well in both cases. So the problem is with some words.
I have found a very interesting example. I use the verb "echar". Among others, it has 2 inflections that are "echaré" and "echaría". If I consult "echaré" with and without an accent, it works well for me and it also returns me between its inflections.
If I consult "echaría" work well with accent and bad without accent.
Video: https://youtu.be/DmfZmzRW4w8
Problem 3: Transact load the synonyms many times.
I have added synonyms and a function that loads them when starting sql server. The problem is that when you make the first consultation you think about 1.5 minutes. Also, if I do not do searches, every so often (it can be 15 minutes) again it seems that it reloads the synonyms, perhaps from tempdb. How is it done to load them once and not delete them from tempdb?
I have this store procedure to load the synonyms at the start of sql server from file:
USE master;
GO
CREATE PROCEDURE Sinonimos
AS
SET NOCOUNT ON;
GO
EXEC sys.sp_fulltext_load_thesaurus_file 3082, @loadOnlyIfNotLoaded = 1;
GO
Activate the store procedure at startup by:
USE master
GO
EXEC sp_procoption @ProcName='Sinonimos', @OptionName = 'startup', @OptionValue = 'on';
GO
Problem 4: You can not protect the synonyms.
I have to install the sql server system on a client but I do not want to leave the .xml file from FTData there with synonyms. Is there a way for the file to load it from a remote URL or can the file be encrypted?
modified 8-Feb-18 7:32am.
|
|
|
|
|
Hello friends!
Could you tell me please how to delete specific data from multiple tables in sql database .
|
|
|
|
|
Very simple:
DELETE FROM table1 WHERE something = somethingelse
DELETE FROM table2 WHERE 1 = 2
DELETE FROM table3 WHERE ...
Everyone is born right handed. Only the strongest overcome it.
Fight for left-handed rights and hand equality.
|
|
|
|
|
I have this function that creates an order. I was missing a couple of things in the columns like weight, cost and profit.
So I wrote this to just grab the cart items to calculate my missing data
List<orders_cart> sC = context.ORDERS_CART.Where(m => m.order_ID == pOrderID).ToList();
But then I came to the ship weight, which I need to get from PRODUCT_INFO.
I forget the nomemclature for my expression above, so I wasn't able to search possibilities.
Possible to do a quick join off sC into another list?
If possible, I would need a little nudge towards getting it right.
Thanks!
If it ain't broke don't fix it
|
|
|
|
|
Presumably you have a navigation property from the cart lines to the product info?
In which case, you should be able to use something like:
context.ORDERS_CART.Include(l => l.Product).Where(... which will load the product details with each line. You can then use the navigation property to access the product details for the line.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
I think I used include in a VB version of a situation that I needed extra details.
Let me give that a spin.
Monday I just looped the cart items and asked for the details and added them.
If it ain't broke don't fix it
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
