I have rather simple CLR function:
[SqlFunction(IsDeterministic = true, DataAccess = DataAccessKind.None)]
public static SqlString GetUserName()
{
return (SqlString)WindowsIdentity.GetCurrent().Name;
}
When I get result from .NET console app, I get correct answer "JungleSektor". However, when SQL Server executes this code, it gives me "NT ServiceMSSQL $ SQL2014". How to get correct result?
Hi
I regularly use the T-SQL date functions to return the 1st of a particualr month.
e.g.
SELECT DATEADD(m,DATEDIFF(m,0,getdate()),0)
returns 2007-05-01 00:00:00.000
i.e the first of the current month at midnight.
However, when I try to use a similar expression as a derived column in SSIS it returns a completely different date.
DATEADD("month",DATEDIFF("month",(DT_DATE)0,GETDATE()),(DT_DATE)0)
returns 30/05/2007 00:00:00
Any ideas why and how I can obtain the first of a particualr month using SSIS derived column?
Working in a project using Entity Framework (Code First)...
Until now the project has been connected to a (generated) SQL Server Compact 4.0 database,
but now we want to connect to a SQL Server (at least 2008R2 since we will use FILESTREAM...)
Our problem right now is the possibility to enter Infinity values into REAL columns in the DB...
It works in the SQL Server Compact but we have not been able to get it to work in 2008R2 or 2014
The insertion of Infinity values is constructed by the Entity Framework (from using float.PositiveInfinity in C#) automatically so I mainly wonder if it at all is possible in a "real" database. Maybe there are some configurations possible to get it to work?
How the code inserted works.....I am trying to do the same thing with
GetProcesses
So I want to insert into a table the results of all the processes on a server and determine if a process/service is running.......
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.IO;
using System.Collections;
[Code] .....
I have a very strange problem when I try to install SQL Express 2014 on 1 PC on my network.
The setup is blocking on the step "rules of functionnality" (I hope it is the good translation ...).
I ha ve a message that indicates that there is a problem the following checking : "It exists a Internet connection with the download center for Microsoft Framework 4.0". At the beginning I had Framework 3.5 and 4.5 installed on the PC.
When I tried to install manually Framework 4.0 I had a message that indicated that the setup was not possible because there was a Superior version. So I uninstall v4.5 and install v4.0 normally.
I run again SQL 2014 Express setup but I have always the same error. I take a look on Internet to look for the same problem but I found nothing.
My stored procedure was running sqlserver 2012 but can't return output parameter value here i paste my SP ...
alter Procedure BD_InsertMobileClient_GpsLogPlanogram
(
@SalesPersonID int,
@MobileRowID int,
@StatusCode int output
)
[Code] .....
I am using a monitoring system where I can monitor a numeric SQL result assuming the result is one field and one row.I would like to do this to say monitor the free available space or percentage on say the Master database. DBCC SQLPERF gives me a few columns and results for all databases on the server.
View 2 Replies View RelatedI have this script in my database, but it always gives 2054 rows back and if I actually DO change something it doesn't even notice...
UPDATE a
SET a.[omschrijving]=SP.[omschrijving]
,a.[verkoopprijs]=SP.[verkoopprijs]
,a.[gewijzigd]=getDate()
FROM [artikelen] a
LEFT OUTER JOIN [Hofstede].[dbo].[sparepartsupdate] SP
ON a.PartNrFabrikant = sp.PartNrFabrikant
WHERE ((A.omschrijving != SP.[omschrijving]) OR (A.[verkoopprijs] != SP.[verkoopprijs]))
Installed SQL Server 2014 CU1. While testing sp_send_dbmail I noticed the query results, when attached are cut off or truncated. Max file size has been 64k -65k. I set the max file size to 104857600 and set @query_no_truncate = 1.
View 9 Replies View RelatedI have 3 Tables
TableA - TAID, Name, LastName
TableB - MaleFriendsName, MaleFriendsLastName [One to many]
TableC - FemaleFriendsName, FemaleFriendsLastName [one to many]
A query returns duplicate results from TableB as well as TableC
TableB and TableC have nothing in common and should not interfere with each other.
with TwoTables as (
select
a.QuickSpec as QuickSpecId,
[Code].....
Resultset returns duplicate values on TableB AND only for 1 record in the results [As per Lynn examples]
why the statement "where WD.WRKD_WORK_DATE BETWEEN DATEADD(DD, -6, GETDATE()) AND GETDATE()) = 0' is successful, but "WD.WRKD_WORK_DATE BETWEEN (DATEADD(DD, -16, GETDATE())) AND (DATEADD(DD, -3, GETDATE()))" is an invalid statement? I've tried a lot of different syntax variations of this statement and cannot get to work between two lookback dates.
View 9 Replies View RelatedI created a simple procedure in SQL Server Management Studio 2014. When I run it I see my query results but not my return value. Do I have to change settings to see the return value?
CREATE PROCEDURE temp
AS
BEGIN
SELECT GetDate()
FROM mytable
RETURN 1
END
EXEC temp
I'm trying to do a Clr function wiht null values but I have an error. My Clr is like this:
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Collections;
using System.Text;
[code]...
A .NET Framework error occurred during execution of user-defined routine or aggregate "function":
System.InvalidCastException: La conversión especificada no es válida.
System.InvalidCastException:
en Microsoft.SqlServer.Server.ValueUtilsSmi.GetSqlInt16(SmiEventSink_Default sink, ITypedGettersV3 getters, Int32 ordinal, SmiMetaData metaData)
en UserDefinedFunctions.function()
Can I use sqlDatareader with a stored procedure to return more than one reultset that I ll be accessing through MyDatareader.MoveNext
Any Little example on both the stored proc and the .Net side
Thanks
Hi,
Can anybody help me with the following problem:
I have Vista Ultimate installed and within Vista .NET Framework 3.0 is installed as a part of the operating system so I can't remove 3.0
Now for MS SQL Server 2005 Express I need .NET Framework 2.0 and the SQL Server 2005 doesn't work with .NET Framework 3.0
Can't install 2.0, get the message that 3.0 is already installed.
Please help me!!
René
I am trying to pivot my data results so that instead of showing multiple rows for each product a client has it will show one line for each client and concatenate all of their products together.
For example, if I run the following query:
SELECT
[Year],
[Client_Name],
[Product_Name]
FROM My.dbo.Table
GROUP BY
[Year],
[Client_Name],
[Product_Name]
I get the following result set:
YearClient_NameProduct_Name
2014BobHosting
2014BobDevelopment
2014BobTesting
2014FredDevelopment
2014FredConsulting
2014MaxConsulting
2015BobHosting
2015BobTesting
What I want to get back as the result set is the following:
YearClient NameProduct-List
2014BobHosting-Development-Testing
2014FredDevelopment-Consulting
2014MaxConsulting
2015BobHosting-Testing
So, I would only get one record back for each Client for each Year with a list of all of their products concatenated together.
I need to find any stored procedures that have not been used over a certain time period.
I have set up an extended events session to gather sp_statement_starting and _completed.
The trace returns the object_id's of many stored procedures. I then query the sys.procedures and plug in the object_id to return the stored proc name.
Do I have to repeat this process for every different object_id or is there a way I can query the trace results, using the object_id as my search criteria as one query ?
We are running SQL 2014 SP1. We are using defined Full text indexes on several tables in the database. However, on one specific set of servers, a certain search will not return any data. This exact same search works on another set of servers built identically. The first responses I'm sure will be stop list, but I have dropped and recreated the FTI multiple times with different stop lists or no stop list at all and get the same results.
The specific word being searched on is YUM. If I change the value to YUMk, it actually returns, and if I change the data to TUM it returns, but YUM does not. This exact query is working on multiple other systems, so it seems to be something environmental, but I haven't been able to pinpoint it.
I am using the QRCode4CS class [URL] .... to generate QR codes.
I can use the following code to successfully return a bitmap image to a picturebox in a Windows Form Application.
public class CreateQRCodeClass
{
public static Image CreateQRCodeImage(string inputString)
{
QRCode4CS.QRCode qrcode = new QRCode4CS.QRCode(new QRCode4CS.Options(inputString));
qrcode.Make();
[Code] ....
In trying to adapt the same code (below) to display a QR code in an SSRS report I get the error "There is an error on line 1 of custom code: [BC30311] Value of type 'System.Drawing.Image' cannot be converted to '1-dimensional array of Byte.'
Here is the custom code I am using.
Public Function QRCode(ByVal RetailerId As String) as Byte()
Return QRCode4CSCreateQRCode.CreateQRCodeClass.CreateQRCodeImage(RetailerId)
End Function
Here is the revised custom assembly.
public class CreateQRCodeClass
{
public static byte[] CreateQRCodeImage(string inputString)
{
QRCode4CS.QRCode qrcode = new QRCode4CS.QRCode(new QRCode4CS.Options(inputString));
qrcode.Make();
Image canvas = new Bitmap(86, 86);
[Code] ....
What data type can I successfully return to SSRS to display the image?
fix the below SP which is having cursor output with an alternate logic which can make the SP work.(may be using temp table or any other option)
CREATE PROCEDURE Get_IDS
/*
* SSMA warning messages:
* O2SS0356: Conversion from NUMBER datatype can cause data loss.
*/
@p_uid float(53),
@return_value_argument varchar(8000) OUTPUT
[code]....
I have a FullTextSQLQuery which I am trying to search a phrase(The Multi-part identifier) on full text indexed table. I am getting expected results on running the below sql query on QA machine and PreProduction servers, but not getting the same results on our development and production servres as even though same code running.
SELECT DISTINCT TOP 50 c.case_id,c.status_id,cal.cas_details
FROM g_case_action_log cal (READUNCOMMITTED)
INNER JOIN g_case c (READUNCOMMITTED) ON (cal.case_id = c.case_id)
INNER JOIN CONTAINSTABLE(es.g_case_action_log, cas_details,
' "The multi-part identifier" OR "<br>The multi-part identifier" OR
"The multi-part identifier<br>" ') as key_tbl
ON cal.log_id = key_tbl.[key]
ORDER By c.case_id DESC
We are using SqlServer 2008 R2 version on all servers.
I'm a Reporting Services New-Be.
I'm trying to create a report that's based on a SQL-2005 Stored Procedure.
I added the Report Designer, a Report dataset ( based on a shared datasource).
When I try to build the project in BIDS, I get an error. The error occurs three times, once for each parameter on the stored procedure.
I'll only reproduce one instance of the error for the sake of brevity.
[rsCompilerErrorInExpression] The Value expression for the query parameter 'UserID' contains an error : [BC30654] 'Return' statement in a Function, Get, or Operator must return a value.
I've searched on this error and it looks like it's a Visual Basic error :
http://msdn2.microsoft.com/en-us/library/8x403818(VS.80).aspx
My guess is that BIDS is creating some VB code behind the scenes for the report.
I can't find any other information that seems to fit this error.
The other reports in the BIDS project built successfully before I added this report, so it must be something specific to the report.
BTW, the Stored Procedure this report is based on a global temp table. The other reports do not use temp tables.
Can anyone help ?
Thanks,
Writing a SQL Function as below
the input parameter for function should be datetime of sql datetimeformat
and out put should be a string = yyyymmdd1 or yyyymmdd2
The last character 1 or 2 based on below condition
if time is between 6AM and 5.59PM then 1
if 6PM to 5.59AM then 2
I have a Javascript Function provided by our .NET Developer Team.
This code has to be used in a "Text Box" and in Text Box "Action Property" this will be added in the "Go To URL" section.
But Since i have no understanding of Javascript i am not been able to add it.I tried a method of adding this code in the expression section, but it gives error of "End of statement" in Preview section.
I have 2 databases on the same server
One has a function, the other has the tables and views
using dba
select dbo.function(t.column) as x from dbb.dbo.table as t
gives m an invalid object name of dbo.table
using dba
select top 1 * from dbb.dbo.table works fine.
also if i create the function on the same db it works.
I'm trying to create a simple function that will do a count on a table. I want to pass the table name in form of a parameter to the variable and this function will return the count as an int. See my function below...
CREATE FUNCTION count_rows (@tablename varchar(100)
RETURNS int AS
BEGIN
DECLARE @emp_count AS int
declare @declaration varchar(100)
[Code] ....
The errors I am getting are as follows:
Msg 102, Level 15, State 1, Procedure count_rows, Line 3
Incorrect syntax near 'RETURNS'.
Msg 102, Level 15, State 1, Procedure count_rows, Line 10
Incorrect syntax near '@declaration'.
Msg 178, Level 15, State 1, Procedure count_rows, Line 14
A RETURN statement with a return value cannot be used in this context.
I have written this sample query to search a full-text indexed table and return the results. If the word occurs more than once I want it to return as a new record and the results show a short summary of the location. I was using 'like', but the full table scans were really slowing it down. Can performance be improved for the following (The results returned by my query are accurate)
Query
DECLARE @searchString nvarchar(255);
DECLARE @searchStringWild nvarchar(275);
SET @searchString = 'first';
SET @searchStringWild = UPPER('"' +@searchString+ '*"');
SELECT id, '...' + SUBSTRING(searchResults.MatchedCell, searchResults.StartPosition, searchResults.EndPosition - searchResults.StartPosition) + '...' as Result
[Code] ....
I have created a function that will check whether the data is null or not. If its null then it will display that as No data else it will display the original value. Below is the function
GO
Object: UserDefinedFunction [dbo].[fnchkNull] Script Date: 3/4/2015 12:01:58 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
[code]...
The code is working good. However i want the return type to be dynamic. If the data type supplied is integer then i want to return a integer value like 0 if its null. if the data value is varchar then i want to return 'No Data'.
I have a heavy database , More than 100 GB only for six month .every Query on it takes me along time and I dont have enough space to add more indexes.by a way I decided to do partitioning. I create a partition function , on date filed and all Data records per month was appointed to a separate file.And is partitioning only for Future data entry?
View 9 Replies View RelatedHi, have configured an ODBC linked server for an Adaptive Server Anywhere (ASA6.0) database.
I have to write a function (not a procedure) that receives a number (@Code) and returns 1 if it was found on a table in the linked server, or 0 if not. Looks very simple...
One problem, is that the queries on a linked-server must be made through the OPENQUERY statement, which doesen't support dynamic parameters. I've solved this making the whole query a string, and executing it, something like this:
SET @SQL='SELECT * FROM OPENQUERY(CAT_ASA, ''SELECT code FROM countries WHERE code=' + @Code + ''')'
EXEC sp_executesql @SQL
(CAT_ASA is the linked-server's name)
Then, i would use @@ROWCOUNT to determine if the code exists or not. But before this, a problem appears: sp_executesql is not allowed within a function (only extended procedures are allowed).
Does somebody know how to make what i want?? I prefer to avoid using temporary tables.
Thanks!
My overarching goal is to generate sets of random Symptom records for each Enrollee in a drug study, so that for each cycle (period of time), the code will insert a random number of random records for each enrollee.
I'm trying to return a number of random records from a table, but inside a table-valued function... (which could be my problem).
CREATE FUNCTION dbo.ufn_GetTopSymptoms (
@enrollID INT
, @CTCVersion VARCHAR(20)
, @NumRecords INT
)
RETURNS TABLE
[Code] ....
But that ORDER BY NEWID() clause is illegal apparently, because here's the error it throws:
Msg 443, Level 16, State 1, Procedure ufn_GetTopSymptoms, Line 13
Invalid use of a side-effecting operator 'newid' within a function.
I was hoping I could return a set of enrollmentIDs and then use CROSS APPLY to generate a random set of records for each enrollmentID... is this not possible with APPLY? I was trying to avoid using a cursor...
The idea is basically to create all the Symptom records for all the patients in treatment cycle at once by using Enrollee OUTER APPLY dbo.ufn_GetTopSymtoms(dbo.Enrollment.EnrolleeID)
but that's clearly not working. Is there a way to do this without resorting to a cursor?
I'm trying to convert the query immediately below into a function with the conditional logic to return a VARCHAR value with the gender: male, female or unknown.
SELECT empid, firstname, lastname, titleofcourtesy,
CASE
WHEN titleofcourtesy IN('Ms.', 'Mrs.') THEN 'Female'
WHEN titleofcourtesy = 'Mr.' THEN 'Male'
ELSE 'Unknown'
END AS gender
FROM HR.Employees;
GO
Below is the conditional logic function I'm trying to create to replicate the logic above.
CREATE FUNCTION dbo.Gender
(
@male AS VARCHAR(10),
@female AS VARCHAR(10),
@unknown AS VARCHAR(10)
)
RETURNS VARCHAR(10)
[Code] .....
I am trying to get this query to the point where it will return only results that meet specific conditions. Here is the current query I have:
use mdb
SELECT call_req.ref_num, prob_ctg.sym, act_type.sym, act_log.description
FROM call_req
INNER JOIN prob_ctg
ON call_req.category = prob_ctg.persid
INNER join act_log
ON call_req.persid = act_log.call_req_id
INNER JOIN act_type
ON act_log.type = act_type.code
where prob_ctg.sym like 'rd1.%' and prob_ctg.auto_assign=1 and call_req.active_flag=0 ORDER BY call_req.ref_num, act_log.time_stamp DESC
This produces results as follows:
I3835493FY15 RD1.Desktop Transfer Transfer Asignee from " to Jordana, Jose"
I3835493FY15 RD1.Desktop Close Status changed from 'Resolved' to 'Closed'
I3835493FY15 RD1.Desktop Close Status changed from 'Open' to 'Resolved'
I3835493FY15 RD1.Desktop Event Occurred AHD05447 No eligible Request Locations
I3835493FY15 RD1.Desktop Initial Create new request/incident/problem
[Code] .....
This query gives me the total number of records that meet my overall criteria, but what I need to have is a count of the total distinct number of call_req.ref_num and then I need a count of the number of records where act_type.code = TR and the text of act_log.description contains text including "Transfer Group From" and then I need a count of the records where act_type.code = EVT and the text of act_log.description contains text including "AHD054".