I migrate from SQL Server to PostgreSQL. How to translate the getutcdate function like:
Insert into mytable (mypk, toucheddate) values (99, getutcdate() )
mercredi 6 mai 2015
What is the equivalence of getutcdate() in postgresql?
how to perfom an update with group by?
I have concerns with an update statement. The primary key of my table is composed of the following columns:
ReferencePiece, NumeroPhase, AncienPoste,DateStrategie, DateCadence, NomProgramme, VersionProgramme.
Here is my procedure:
update S
set S.stock=T.Stock-T.NombrePiecesParAvion*T.CadenceProgramme
from SITUATIONS_Final_1 S inner join SITUATIONS_Final_1 T
on S.ReferencePiece=T.ReferencePiece and S.NumeroPhase=T.NumeroPhase and S.AncienPoste=T.AncienPoste
and T.DateDebProduction<S.DateDebProduction and T.stock = (select min(T.stock) from SITUATIONS_Final_1 T)
where S.DateDebProduction=(select min(S.datedebproduction) as 'date1'
from SITUATIONS_Final_1 S inner join SITUATIONS_Final_1 T
on S.ReferencePiece=T.ReferencePiece and S.NumeroPhase=T.NumeroPhase and S.AncienPoste=T.AncienPoste
where S.datedebproduction>T.DateDebProduction and T.stock = (select min(T.stock) from SITUATIONS_Final_1 T))
But actually, as there is no 'group by' on my update statement, It only takes into account the Min(stock) of all my references and do the update for the future dates.
I tried to do it as following but I'm sure it's much easier than that :
update S
set S.stock=T.Stock-T.NombrePiecesParAvion*T.CadenceProgramme
from SITUATIONS_Final_1 S inner join SITUATIONS_Final_1 T
on S.ReferencePiece=T.ReferencePiece and S.NumeroPhase=T.NumeroPhase and S.AncienPoste=T.AncienPoste
and T.DateDebProduction<S.DateDebProduction inner join (select referencepiece,NumeroPhase,AncienPoste,DateStrategie,
nomprogramme,versionprogramme, datedebproduction, min(stock) StockMini from SITUATIONS_Final_1 group by ReferencePiece,NumeroPhase,AncienPoste,DateStrategie,
nomprogramme,versionprogramme,DateDebProduction)
F on F.ReferencePiece=T.ReferencePiece and T.Stock=F.StockMini
inner join (select referencepiece,NumeroPhase,AncienPoste,DateStrategie,
nomprogramme,versionprogramme, min(datedebproduction) datemini,stock
from SITUATIONS_Final_1 group by ReferencePiece,NumeroPhase,AncienPoste,DateStrategie,
nomprogramme,versionprogramme,DateDebProduction,stock) Z on F.ReferencePiece=Z.ReferencePiece
where T.DateDebProduction=Z.datemini and Z.datemini=F.DateDebProduction and Z.Stock=F.StockMini and T.ReferencePiece=Z.ReferencePiece and S.datedebproduction>T.DateDebProduction
and T.stock=F.StockMini and S.ReferencePiece=T.ReferencePiece and T.ReferencePiece=F.ReferencePiece and F.numerophase=Z.numerophase
and T.numerophase=Z.numerophase and F.AncienPoste=Z.AncienPoste and T.AncienPoste=Z.AncienPoste and F.DateStrategie=Z.DateStrategie and T.DateStrategie =Z.DateStrategie
and F.nomprogramme=Z.nomprogramme and T.nomprogramme=Z.nomprogramme and F.versionprogramme=Z.versionprogramme and T.versionprogramme=Z.versionprogramme
and S.ReferencePiece=T.ReferencePiece and S.NumeroPhase=T.NumeroPhase and S.AncienPoste=T.AncienPoste and S.nomprogramme=T.nomprogramme
and S.versionprogramme=T.versionprogramme and S.DateStrategie=T.datestrategie
But it doesn't perform well: It updates the same rows (the sames dates). I need this procedure to do something like that:
Referencepiece=RefA DateDebProduction='02/05/2015' Stock=4 NbrePieces=2 CadenceProgramme=1
For the same reference and the next date The stock should be= 4-2*1=2... Actually, this procedure is contained on another one. It launches it 5 times so that I can be sure that It will update all my rows. If you could help me finding a better way to update all rows without launching my procedure many times it would be great.
Here is a sample of data:
ReferencePiece NumeroPhase AncienPoste DateStrategie Charge NomProgramme VersionProgramme CadenceProgramme DateCadence DateDebProduction NombrePiecesParAvion Stock
RefA001 10 114179A 02/05/2015 0.000 Z540 A BGR 29.000 01/03/2015 01/03/2015 1.000 23.000
RefA001 10 114179A 02/05/2015 0.000 Z540 A BGR 25.000 01/04/2015 01/04/2015 1.000 NULL
RefA001 10 114179A 02/05/2015 0.000 Z540 A BGR 19.000 01/05/2015 01/05/2015 1.000 NULL
RefA001 10 114179A 02/05/2015 0.000 Z540 A BGR 25.000 01/06/2015 01/06/2015 1.000 NULL
RefXY001 10 1MR704 02/05/2015 0.220 X220 RM 2.000 01/05/2015 01/05/2015 2.000 12.000
RefXY001 10 1MR704 02/05/2015 0.220 X220 RM 2.000 01/06/2015 01/06/2015 2.000 8.000
RefXY001 10 1MR704 02/05/2015 0.220 X220 RM 2.000 01/07/2015 01/07/2015 2.000 NULL
RefXY001 10 1MR704 02/05/2015 0.220 X220 RM 3.000 01/08/2015 01/08/2015 2.000 NULL
As you can notice in this sample (where I've launched my procedure once), It updated the second row of the reference 'RefXY001' where stock=12-2*2=8, because 12<23. As I run this procedure, It will update only future dates of reference 'RefXY001' because the smallest stock is its.
Hibernate is generating wrong jointable
So I'm trying to lay a onetomany relationship between 2 tables in a different database, I have the following mapping:
@OneToMany
@JoinTable(
name = "CountryEntityCountry",
joinColumns = @JoinColumn(name = "countryEntityId"),
inverseJoinColumns = @JoinColumn(name = "countryId")
)
@Cache(usage = CacheConcurrencyStrategy.READ_WRITE)
public Set<Country> getDestinationCountries() {
return destinationCountries;
}
countryEntityId points back to my own entity CountryEntity, countryId points to a table in another database. Obviously I'd want hibernate to generate a clustered primary key, this is the sql generated by hibernate in MS SQL server:
USE [dbNcrt]
GO
/****** Object: Table [dbo].[CountryEntityCountry] Script Date: 05/06/2015 08:51:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[CountryEntityCountry](
[countryEntityId] [numeric](19, 0) NOT NULL,
[countryId] [numeric](19, 0) NOT NULL,
PRIMARY KEY CLUSTERED
(
[countryEntityId] ASC,
[countryId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [FG_DATA],
UNIQUE NONCLUSTERED
(
[countryId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_R
OW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [FG_DATA]
) ON [FG_DATA]
GO
ALTER TABLE [dbo].[CountryEntityCountry] WITH CHECK ADD CONSTRAINT [FK6DAE065D46A920C0] FOREIGN KEY([countryEntityId])
REFERENCES [dbo].[CountryEntity] ([id])
GO
ALTER TABLE [dbo].[CountryEntityCountry] CHECK CONSTRAINT [FK6DAE065D46A920C0]
GO
Now for some reason hibernate is adding a unique constraint to the key on the MANY side. I don't mind having hibernate generate a unique constraint, but I'd appreciate it if it could be on the correct side, not on the many side, obviously I will have rows like:
- 1 245
- 1 234
- 2 245 -> Crash here already because of Hibernate's wrongfully generated unique constraint
Why is hibernate doing this? Is there something wrong with my mapping? I even tried adding explicitly unique=false to the countryId @JoinColumn, also tried adding the target entity explicitly, both of which hibernate seemed to have ignored.
Create random function that return DateTime in range : Invalid use of a side-effecting operator
Consider the SQL Server code :
CREATE FUNCTION dbo.CreateRandomDateTime ()
RETURNS DateTime
AS BEGIN
DECLARE @DateFrom DATETime = '2012-01-01'
DECLARE @DateTo DATeTime = '2012-06-30'
DECLARE @DaysRandom Int= 0
DECLARE @MillisRandom Int=0
select @DaysRandom= DATEDIFF(day,@DateFrom,@DateTo)
SELECT @DaysRandom = ROUND(((@DaysRandom -1) * RAND()), 0)
--get random millis
SELECT @MillisRandom = ROUND(((99999999) * RAND()), 0)
SELECT @DateTo = DATEADD(day, @DaysRandom, @DateTo)
SELECT @DateTo = DATEADD(MILLISECOND, @MillisRandom, @DateTo)
RETURN @DateTo
END
I want to create a function that returns a random DateTime , but when I execute the code I get :
Msg 443, Level 16, State 1, Procedure CreateRandomDateTime, Line 9
Invalid use of a side-effecting operator 'rand' within a function.
Msg 443, Level 16, State 1, Procedure CreateRandomDateTime, Line 12
Invalid use of a side-effecting operator 'rand' within a function.
Msg 8116, Level 16, State 1, Procedure CreateRandomDateTime, Line 14
Argument data type datetime is invalid for argument 2 of dateadd function.
Msg 8116, Level 16, State 1, Procedure CreateRandomDateTime, Line 15
Argument data type datetime is invalid for argument 2 of dateadd function.
But when I run the code without the body function , I get a random datetime , so it seems that I did something wrong with the function .
Where did I go wrong ?
Create Surrogate Primary Key
Let's say I have table as follows:
FirstName MiddleName LastName EmailAddress
John Middle Steward some@thing.com
Alice Wonder Land some@thing.com
Steve Mac Qwak some@thing.com
Miss Mon Cherie some@thing.com
Liz Sea Shell some@thing.com
This is data source with employees. As you can see, there is no primary key. Is there any way how to enhance these data with some kind of surrogate primary key? Note this is data source. I assume it is not possible to create any primary key out of the data as any time one of the row / column change, this will also lead to change of the surrogate primary key. Correct? Or is there any way?
These data are meant to be loaded into DWH using SSIS. As there should be some Lookup transformation, I need to have primary key in the source data set.
mardi 5 mai 2015
How to create this complex Result using SQL
I have a sales amount table need to generate a report like this in sql server
QTY Amount % Amount
5 5000 35%
10 10000 65%
15 15000 100%
The last row is total of all from top two rows.
35% is 5000/15000
67% is 10000/15000
How can i create a query for this ?
how to restrict sql database to be opened in other servers?
Currently i have a database that is attached to a named instance server with login credentials ,but at the same time if i attach the database to another server in other system ,the same gets opened.
How to deal with this?
Is there a way to restrict the database file to opened by others without valid credentials ?
Complex Sql Query for Sales Commison Calculation Multi Level
I'm trying to get employees report which shows their commission.
CREATE TABLE [dbo].[Employee] (
[Id] INT NOT NULL,
[firstname] VARCHAR (100) NULL,
[lastname] VARCHAR (100) NULL,
[promoterID] INT NULL,
[titleID] INT NULL,
PRIMARY KEY CLUSTERED ([Id] ASC)
);
insert into employee(id,firstname,lastname,promoterid) values(1,'King1','Knight1',null,1)
insert into employee(id,firstname,lastname,promoterid) values(2,'King2','Knight2',1,2)
insert into employee(id,firstname,lastname,promoterid) values(3,'King3','Knight3',1,1)
insert into employee(id,firstname,lastname,promoterid) values(4,'King4','Knight4',2,3)
insert into employee(id,firstname,lastname,promoterid) values(5,'King5','Knight5',4,2)
CREATE TABLE [dbo].[sales_product] (
[Id] INT IDENTITY (1, 1) NOT NULL,
[customerid] INT NULL,
[employeeid] INT NULL,
[commision] FLOAT (53) NULL,
PRIMARY KEY CLUSTERED ([Id] ASC)
);
insert into sales_product(id,customerid,employeeid,commision) values(1,2,5,100)
insert into sales_product(id,customerid,employeeid,commision) values(2,3,3,200)
insert into sales_product(id,customerid,employeeid,commision) values(3,5,2,100)
insert into sales_product(id,customerid,employeeid,commision) values(4,6,5,400)
insert into sales_product(id,customerid,employeeid,commision) values(5,7,1,500)
CREATE TABLE [dbo].[title] (
[Id] INT NOT NULL,
[title_code] VARCHAR (200) NULL,
[onA] INT NULL,
[onEC] INT NULL,
[onJSC] INT NULL,
PRIMARY KEY CLUSTERED ([Id] ASC)
);
insert into title(id,title_code,<Dynamic Columns that gets created on adding new title whose name is "on" + title_code>) values(1,'A',20,30,10)
insert into title(id,title_code,<Dynamic Columns that gets created on adding new title whose name is "on" + title_code>) values(2,'EC',20,30,10)
insert into title(id,title_code,<Dynamic Columns that gets created on adding new title whose name is "on" + title_code>) values(3,'JSC',20,30,10)
insert into title(id,title_code,<Dynamic Columns that gets created on adding new title whose name is "on" + title_code>) values(4,'SSC',20,30,10,0)
Required Output:
firstname lastname totalcommison(employee commision + sum of (junior commsions*commision % based on title of, i.e if employee title_code is A than it will look for % on juniors title code ie onEC or JSC... which we get by getting juniors title code))
So far i'm able to get all the boses of employee in heirarchy
WITH EmployeeAncestor(id, firstname, promoterid)
AS
(
select id, firstname, promoter from employee WHERE id = 4
union all
select m.id, m.firstname, m.promoterid
from EmployeeAncestor CTE,employeem
WHERE CTE.promoter = m.id
)
SELECT id, firstname, promoterid FROM EmployeeAncestor ORDER BY id ASC
I have following tables:
- sales_product - where i store employee sales and commission he gets
Column Names : salesid, employeeid, productid, commision
- Employee - this table stores employee detail, title id and promoter Id i.e, boss of the employee.
Column Names : employeeid, promoterid(employee id of manager), firstname, lastname, titleid
- title - where i store titles of the employee for example general manger, junior sales consultant etc and commission % on the different titles
For example
titleId Title CommisiononJuniorSalesCons CommisiononSeniorSalesCons
1. General Manager 10 50
2. Junior Sales Cons 0 5
Now I want to create a SQL query and display it in a table where i get employee name and total commission he gets on his juniors. The commission will be calculated by multiplying the commission on product from sales_product table and % commission on employee sale from title and then we sum that commission and we get total commission earned by the employee.
How to create login for SQL Server with restricted permissions for an AD group?
I am trying to create a LOGIN to SQL Server using an Active Directory Group. This group should have the least amount of privileges required to perform their required tasks. The members of this group are users that monitor and operate a lot of the administrative tasks on the database but should not be allowed to view database content. They will need to be able to view the server state, perform traces, add databases to availability groups, restore databases and view metadata though. With below script the member of the group can view the server state etc. using a member that belongs to the group but I am not able to see metadata or add databases to availability groups. If I run the same script but create a LOGIN for a user instead of a group it works much better.
What am I missing in order to get this to work for a AD group instead of a user? The error that I get when logging in with user account belonging to the group is as follows:
Error: "The server principal "MACHINENAME\USERNAME" is not able to access the database "AdventureWorks2012" under the current security context. (Microsoft SQL Server, Error: 916)"
Script:
USE [master];
IF NOT EXISTS( SELECT *
FROM sys.server_principals
WHERE name = N'BUILTIN\Users' )
BEGIN
DECLARE @CreateError INT;
CREATE LOGIN [BUILTIN\Users] FROM WINDOWS;
SET @CreateError = @@ERROR;
IF(@CreateError = 0)
BEGIN EXEC master.dbo.sp_addsrvrolemember
@loginame = 'BUILTIN\Users',
@rolename = 'OperationRole';
END;
GRANT CONNECT SQL TO [BUILTIN\Users];
USE [master];
IF NOT EXISTS( SELECT *
FROM sys.database_principals
WHERE name = N'BUILTIN\Users' )
CREATE USER [BUILTIN\Users] FOR LOGIN [BUILTIN\Users] WITH DEFAULT_SCHEMA = [dbo];
IF NOT EXISTS( SELECT *
FROM sys.database_principals
WHERE name = N'OperationRole'
AND type = 'R' )
BEGIN
CREATE ROLE [OperationRole] AUTHORIZATION [dbo];
GRANT EXECUTE ON [dbo].[CommandExecute] TO [OperationRole];
GRANT VIEW DEFINITION ON [dbo].[CommandExecute] TO [OperationRole];
GRANT EXECUTE ON [dbo].[DatabaseBackup] TO [OperationRole];
GRANT VIEW DEFINITION ON [dbo].[DatabaseBackup] TO [OperationRole];
GRANT EXECUTE ON [dbo].[DatabaseIntegrityCheck] TO [OperationRole];
GRANT VIEW DEFINITION ON [dbo].[DatabaseIntegrityCheck] TO [OperationRole];
GRANT EXECUTE ON [dbo].[DatabaseRestore] TO [OperationRole];
GRANT VIEW DEFINITION ON [dbo].[DatabaseRestore] TO [OperationRole];
GRANT EXECUTE ON [dbo].[GenerateRestoreScript] TO [OperationRole];
GRANT VIEW DEFINITION ON [dbo].[GenerateRestoreScript] TO [OperationRole];
GRANT EXECUTE ON [dbo].[sp_BlitzCache] TO [OperationRole];
GRANT VIEW DEFINITION ON [dbo].[sp_BlitzCache] TO [OperationRole];
GRANT EXECUTE ON [sys].[sp_readerrorlog] TO [OperationRole];
GRANT EXECUTE ON [dbo].[sp_WhoIsActive] TO [OperationRole];
GRANT VIEW DEFINITION ON [dbo].[sp_WhoIsActive] TO [OperationRole];
END;
IF EXISTS( SELECT *
FROM sys.database_principals
WHERE name = N'OperationRole'
AND type = 'R' ) EXEC sp_addrolemember
N'OperationRole',
N'BUILTIN\Users';
USE [msdb];
IF NOT EXISTS( SELECT *
FROM sys.database_principals
WHERE name = N'BUILTIN\Users' )
CREATE USER [BUILTIN\Users] FOR LOGIN [BUILTIN\Users] WITH DEFAULT_SCHEMA = [dbo];
IF NOT EXISTS( SELECT *
FROM sys.database_principals
WHERE name = N'SQLAgentUserRole'
AND type = 'R' )
BEGIN
CREATE ROLE [SQLAgentUserRole] AUTHORIZATION [dbo];
GRANT EXECUTE ON [dbo].[sp_add_job] TO [SQLAgentUserRole];
GRANT EXECUTE ON [dbo].[sp_add_jobschedule] TO [SQLAgentUserRole];
GRANT EXECUTE ON [dbo].[sp_add_jobserver] TO [SQLAgentUserRole];
GRANT EXECUTE ON [dbo].[sp_add_jobstep] TO [SQLAgentUserRole];
GRANT EXECUTE ON [dbo].[sp_add_schedule] TO [SQLAgentUserRole];
GRANT EXECUTE ON [dbo].[sp_addtask] TO [SQLAgentUserRole];
GRANT EXECUTE ON [dbo].[sp_attach_schedule] TO [SQLAgentUserRole];
GRANT EXECUTE ON [dbo].[sp_check_for_owned_jobs] TO [SQLAgentUserRole];
GRANT EXECUTE ON [dbo].[sp_check_for_owned_jobsteps] TO [SQLAgentUserRole];
GRANT EXECUTE ON [dbo].[sp_delete_job] TO [SQLAgentUserRole];
GRANT EXECUTE ON [dbo].[sp_delete_jobschedule] TO [SQLAgentUserRole];
GRANT EXECUTE ON [dbo].[sp_delete_jobserver] TO [SQLAgentUserRole];
GRANT EXECUTE ON [dbo].[sp_delete_jobstep] TO [SQLAgentUserRole];
GRANT EXECUTE ON [dbo].[sp_delete_jobsteplog] TO [SQLAgentUserRole];
GRANT EXECUTE ON [dbo].[sp_delete_schedule] TO [SQLAgentUserRole];
GRANT EXECUTE ON [dbo].[sp_detach_schedule] TO [SQLAgentUserRole];
GRANT EXECUTE ON [dbo].[sp_droptask] TO [SQLAgentUserRole];
GRANT EXECUTE ON [dbo].[sp_enum_sqlagent_subsystems] TO [SQLAgentUserRole];
GRANT EXECUTE ON [dbo].[sp_get_job_alerts] TO [SQLAgentUserRole];
GRANT EXECUTE ON [dbo].[sp_get_jobstep_db_username] TO [SQLAgentUserRole];
GRANT EXECUTE ON [dbo].[sp_get_sqlagent_properties] TO [SQLAgentUserRole];
GRANT EXECUTE ON [dbo].[sp_help_category] TO [SQLAgentUserRole];
GRANT EXECUTE ON [dbo].[sp_help_job] TO [SQLAgentUserRole];
GRANT EXECUTE ON [dbo].[sp_help_jobactivity] TO [SQLAgentUserRole];
GRANT EXECUTE ON [dbo].[sp_help_jobcount] TO [SQLAgentUserRole];
GRANT EXECUTE ON [dbo].[sp_help_jobhistory] TO [SQLAgentUserRole];
GRANT EXECUTE ON [dbo].[sp_help_jobhistory_full] TO [SQLAgentUserRole];
GRANT EXECUTE ON [dbo].[sp_help_jobhistory_sem] TO [SQLAgentUserRole];
GRANT EXECUTE ON [dbo].[sp_help_jobhistory_summary] TO [SQLAgentUserRole];
GRANT EXECUTE ON [dbo].[sp_help_jobs_in_schedule] TO [SQLAgentUserRole];
GRANT EXECUTE ON [dbo].[sp_help_jobschedule] TO [SQLAgentUserRole];
GRANT EXECUTE ON [dbo].[sp_help_jobserver] TO [SQLAgentUserRole];
GRANT EXECUTE ON [dbo].[sp_help_jobstep] TO [SQLAgentUserRole];
GRANT EXECUTE ON [dbo].[sp_help_jobsteplog] TO [SQLAgentUserRole];
GRANT EXECUTE ON [dbo].[sp_help_operator] TO [SQLAgentUserRole];
GRANT EXECUTE ON [dbo].[sp_help_proxy] TO [SQLAgentUserRole];
GRANT EXECUTE ON [dbo].[sp_help_schedule] TO [SQLAgentUserRole];
GRANT EXECUTE ON [dbo].[sp_maintplan_subplans_by_job] TO [SQLAgentUserRole];
GRANT EXECUTE ON [dbo].[sp_notify_operator] TO [SQLAgentUserRole];
GRANT EXECUTE ON [dbo].[sp_start_job] TO [SQLAgentUserRole];
GRANT EXECUTE ON [dbo].[sp_stop_job] TO [SQLAgentUserRole];
GRANT EXECUTE ON [dbo].[sp_uniquetaskname] TO [SQLAgentUserRole];
GRANT EXECUTE ON [dbo].[sp_update_job] TO [SQLAgentUserRole];
GRANT EXECUTE ON [dbo].[sp_update_jobschedule] TO [SQLAgentUserRole];
GRANT EXECUTE ON [dbo].[sp_update_jobstep] TO [SQLAgentUserRole];
GRANT EXECUTE ON [dbo].[sp_update_schedule] TO [SQLAgentUserRole];
GRANT SELECT ON [dbo].[syscategories] TO [SQLAgentUserRole];
GRANT SELECT ON [dbo].[sysjobs_view] TO [SQLAgentUserRole];
GRANT SELECT ON [dbo].[sysschedules_localserver_view] TO [SQLAgentUserRole];
ALTER AUTHORIZATION ON SCHEMA ::[SQLAgentUserRole] TO [SQLAgentUserRole];
END;
IF EXISTS( SELECT *
FROM sys.database_principals
WHERE name = N'SQLAgentUserRole'
AND type = 'R' ) EXEC sp_addrolemember
N'SQLAgentUserRole',
N'BUILTIN\Users';
IF NOT EXISTS( SELECT *
FROM sys.database_principals
WHERE name = N'SQLAgentReaderRole'
AND type = 'R' )
BEGIN
CREATE ROLE [SQLAgentReaderRole] AUTHORIZATION [dbo];
ALTER AUTHORIZATION ON SCHEMA ::[SQLAgentReaderRole] TO [SQLAgentReaderRole];
END;
IF EXISTS( SELECT *
FROM sys.database_principals
WHERE name = N'SQLAgentReaderRole'
AND type = 'R' ) EXEC sp_addrolemember
N'SQLAgentReaderRole',
N'BUILTIN\Users';
IF NOT EXISTS( SELECT *
FROM sys.database_principals
WHERE name = N'SQLAgentOperatorRole'
AND type = 'R' )
BEGIN
CREATE ROLE [SQLAgentOperatorRole] AUTHORIZATION [dbo];
GRANT EXECUTE ON [dbo].[sp_enum_login_for_proxy] TO [SQLAgentOperatorRole];
GRANT EXECUTE ON [dbo].[sp_help_alert] TO [SQLAgentOperatorRole];
GRANT EXECUTE ON [dbo].[sp_help_notification] TO [SQLAgentOperatorRole];
GRANT EXECUTE ON [dbo].[sp_help_targetserver] TO [SQLAgentOperatorRole];
GRANT EXECUTE ON [dbo].[sp_purge_jobhistory] TO [SQLAgentOperatorRole];
GRANT SELECT ON [dbo].[sysalerts] TO [SQLAgentOperatorRole];
GRANT SELECT ON [dbo].[sysalerts_performance_counters_view] TO [SQLAgentOperatorRole];
GRANT SELECT ON [dbo].[sysnotifications] TO [SQLAgentOperatorRole];
GRANT SELECT ON [dbo].[sysoperators] TO [SQLAgentOperatorRole];
ALTER AUTHORIZATION ON SCHEMA ::[SQLAgentOperatorRole] TO [SQLAgentOperatorRole];
END;
IF EXISTS( SELECT *
FROM sys.database_principals
WHERE name = N'SQLAgentOperatorRole'
AND type = 'R' ) EXEC sp_addrolemember
N'SQLAgentOperatorRole',
N'BUILTIN\Users';
IF EXISTS( SELECT *
FROM sys.database_principals
WHERE name = N'db_backupoperator'
AND type = 'R' ) EXEC sp_addrolemember
N'db_backupoperator',
N'BUILTIN\Users';
END;
GO
ALTER SERVER ROLE dbcreator ADD MEMBER [BUILTIN\Users] ;
GO
GRANT ALTER ANY AVAILABILITY GROUP TO [BUILTIN\Users];
GO
GRANT ALTER ANY DATABASE TO [BUILTIN\Users];
GO
GRANT ALTER TRACE TO [BUILTIN\Users];
GO
GRANT CREATE ANY DATABASE TO [BUILTIN\Users];
GO
GRANT CREATE AVAILABILITY GROUP TO [BUILTIN\Users];
GO
GRANT VIEW ANY DEFINITION TO [BUILTIN\Users];
GO
GRANT VIEW SERVER STATE TO [BUILTIN\Users];
GO
I have realized that the reason why I am unable to view the metadata is because I the user does not exist in each database. So I would need to create that user for each database with no privileges in order to see the metadata. Unfortunately the following code is not working for me.
Could you help me get the below code working?
DECLARE @DB_Name varchar(100)
DECLARE @Command nvarchar(200)
DECLARE database_cursor CURSOR FOR
SELECT name
FROM MASTER.sys.sysdatabases
where dbid > 4
OPEN database_cursor
FETCH NEXT FROM database_cursor INTO @DB_Name
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @Command = 'IF NOT EXISTS( SELECT * FROM sys.database_principals
WHERE name = N''BUILTIN\Users'')
CREATE USER [BUILTIN\Users] FOR LOGIN [BUILTIN\Users] WITH DEFAULT_SCHEMA = [dbo]'
EXEC sp_executesql @Command
FETCH NEXT FROM database_cursor INTO @DB_Name
END
CLOSE database_cursor
DEALLOCATE database_cursor
Timeout Error in Stored Procedure in C#
i have this stored procedure to retreave data from database (dynamic query). and i am calling this stored procedure from c# codebehind, i am passsing two parameters from c# to this stored procedure.
alter procedure [dbo].[GetCompleteCPTDetails]
@Practice_Short_Name varchar(50),
@Uploaded_Date nvarchar(max)
as
begin
DECLARE @CPTtablename nvarchar(300)
DECLARE @vQuery NVARCHAR(max)
DECLARE @upldate nvarchar(100)
set @upldate = @Uploaded_Date
set @CPTtablename ='ACER_CLAIMS_MASTER_DETAIL_Hist_'+@Practice_Short_Name
SET @vQuery = 'select Practice_Short_Name,Service_Date_From,Carrier_Name,
Location_Description,Patient_Number,Patient_First_Name,
Patient_Last_Name,Voucher_Number,Procedure_Code,Service_Fees,
Service_Payments,Service_Adjustments,Acer_Status,Acer_Allowed_Amount
from ' +@CPTtablename+'
where Uploaded_Date =''' + @upldate + '''
order by acer_status asc, Service_Date_From desc, Patient_First_Name asc'
EXEC (@vQuery)
end
GO
but when i am running this query it is giving me TimeOut error. but if i assign value to my parameters in SP and Run SP from query windows then it is showing correct data.
can any please explain me why it is giving timeout error if i am calling it from C# codebehind.
fatal error occurred while reading the input stream from the network
fatal error occurred while reading the input stream from the network. The session will be terminated. How important is this type of error? It's happens every 3:e day on production server. At the moment i looks that is has no bigger inpact on the server. It's virtual machin running Windows Server 2008 R2 Sp 1 with Microsoft SQL Server 2005 - 9.00.5000.00 (X64).
After reading kb942861, and reading other post
After checking TCP Chimney Offload is set to automatic?
Shall we stop the producton or is this type of error that the server is able to work without the suggested update of the server? I understand the error but need some more info about if we shall care?
Order in for xml T-sql
select a.Hall, a.Title,
STUFF((SELECT ', ' + '[' + CONVERT(varchar(2),DATEPART(Hour, b.StartFilm))
+ ':' + CONVERT(varchar(2),DATEPART(Minute, b.StartFilm))
+ ' ' + CONVERT(varchar(2),DATEPART(Hour, b.EndTime))
+ ':' + CONVERT(varchar(2),DATEPART(Minute, b.EndTime))
+ ']'
FROM (select c.Name as Hall, b.Title,
Convert(time,a.StartFilmTime) as StartFilm,
Convert(time,a.EndFilmTime) as EndTime
from FilmSchedule a
left join Film b on a.FilmId = b.Id
left join Room c on a.RoomId = c.Id
where a.ApproveStatus = 1 and a.Status = 1 and CONVERT(date, a.StartFilmTime) = '05-06-2015'
) b
Where a.Hall = b.Hall and a.Title = b.Title
FOR XML PATH('')),1,1,'') As ShowTime
from (select c.Name as Hall, b.Title,
Convert(time,a.StartFilmTime) as StartFilm,
Convert(time,a.EndFilmTime) as EndTime
from FilmSchedule a
left join Film b on a.FilmId = b.Id
left join Room c on a.RoomId = c.Id
where a.ApproveStatus = 1 and a.Status = 1 and CONVERT(date, a.StartFilmTime) = '05-06-2015'
Order by a.StartFilmTime
) a
group by a.Hall, a.Title
i get the error:
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.
Help me! pls
Using SQL Server, how to return a list from a While loop, and should I be using a While loop?
I have table A, with columns Id and ParentId. Given an Id, I want to return all ParentId.
Meaning, given an Id, I want to return all entries' ParentId, and then I want to check to see if the recorded ParentId is entered into the table as an Id, if so I want to find it's ParentId, etc...
For example, if I have the simple example of a table like:
Id | ParentID
---+---------
1 0
2 1
3 2
4 3
5 4
6 5
7 6
My query to print all the parents of Id=7 looks like this:
DECLARE @ParentId INT;
SET @ParentId = 7;
WHILE(@ParentId > 0)
BEGIN
SELECT @ParentId = ParentId
FROM A
WHERE Id = @ParentID
PRINT @ParentId
END
This query prints all the ParentId. But, is there a way to return the IDs? In that, if I was calling this query from PHP I would want to get a list of the ParentIds, but my query doesn't actually return anything.
Additionally, I wonder if a while loop is the correct solution. Would a recursive query be faster? If so, any advice on how to convert this to a recursive query?
*Edit
I need to access the list of ParentIds from PHP. Now I'm trying to build a comma-delimited list of Ids to return from the query, which I can later parse in PHP. However, the below query gives me error:
A RETURN statement with a return value cannot be used in this context.
DECLARE @ParentId INT, @ParentList varchar(max);
SET @ParentId = 7;
WHILE(@ParentId > 0)
BEGIN
SELECT @ParentId = ParentId
FROM A
WHERE Id = @ParentID
SET @ParentList = @ParentList | CAST(@ParentId as varchar(20)) | ',';
END
RETURN @ParentList;
What should I do to return a list? Additionally, how can I get the returned value when I execute this statement in PHP?
sql server profiler: How to know which db is being hit using sql server profiler
I have 2 application, connected to 2 different databases on one instance.
I am trying to trace some functionality using SQL Server Profiler(Standard Template) but difficult to understand which database is being hit by one application just by looking into the trace.
Kindly suggest how to know the dbname being hit by one application.
Find all Records that do not have a Foreign letter in a Sql Table
I have been looking and doing research, and I am having trouble trying to split a table to two files, where one file only have English letters and special characters (such as ,.& () 0-9 - etc) and a second file that has all the records that have a foreign letter.
I have tried veriations of
SELECT * FROM TABLE WHERE Column_name like '%[a-zA-Z0-9]%'
but that would not get special characters
also
SELECT * FROM TABLE WHERE Column_name like '%[\040-\176]%'
The data looks like this (not actual Data)
Doha, The Black Pearl Jefferson City & Wells Wenston 89-100 St. Winchester (T) Piñata Valley Not süre how to Üse that U
I have 4000 records and want to quickly look through the table. I want all the records but the last two.
Thank you in advance.
Fill Combobox with Data from Database
I have a Windows Form and a database on SQL Server, called RecordStore. I have a table of CDs (called [CD-table]) I am trying to pull data from and display in this form.
I would like to have the form have a combobox that displays the CD album title ("CDname"), but I would like to be able to get the CD ID # ("CDID") from the Combobox.SelectedValue property - which I would cast as an int.
Currently, I am not sure how I can get both pieces of data in unless I made it into an object, but then it is just displaying ProjectName.CD in the combobox. I want to access the CDID as I have a DataGridView that I would like to fill with just CD data, which I would set as a DataTable, then set that DataTable as the DataGridView's DataSource
The method getting the CD Data and returning a list of CD objects.
private string myconnectionString = @"/*connection string*/";
public List<CD> GetCDList()
{
List<CD> CDList = new List<CD>();
SqlConnection myConnection;
SqlCommand myCmd;
SqlDataReader myReader;
myConnection = new SqlConnection(myconnectionString);
myCmd = new SqlCommand("SELECT CDID, CDname FROM [CD-table]", myConnection);
try
{
myConnection.Open();
myReader = myCmd.ExecuteReader();
while (myReader.Read())
{
CD newCD = new CD((int)myReader["CDID"]);
newCD.name = myReader["CDname"].ToString().Trim();
CDList.Add(newCD);
}
myReader.Close();
myConnection.Close();
}
finally
{
myConnection.Close();
}
return CDList;
}
The CD object (very basic):
public class CD
{
public string name;
private int ID;
public CD(int _ID)
{
ID = _ID;
}
}
and the code filling the Combobox on the Form load method:
List<CD> myList = myDataAccess.GetCDList();
AlbumCombobox.DataSource = myList;
How to update records in an Access subform based on a query?
I have an access form which uses a query looking into the SQL database (using connection string via ODBC). When I load the form, I can only view the data in each record. How can I make my form allow changing the values of the existing record fields, or adding new records?
Among the form properties, the "Allow Additions", "Allow Deletions", "Allow Edits", and "Allow Filters" are all set to "yes". I have tried all the possibles values for "Data Entry" and "Record Lock" but no luck.
Help is appreciated.
Laravel & freetds can't insert data into mssql
I simply need to insert data(charset UTF-8), into mssql table. I can select data, but can't insert.
For connect to mssql server through laravel i use "Freetds".
Laravel 4.2
Insert code:
try {
$data = DB::table('angularTableUserData')->insert([
'userId' => Auth::user()->PK_psPersonalData,
'fullname2' => $data['fullname2'],
'praddress' => $data['praddress'],
'occupation' => $data['occupation'],
'gender' => $data['gender'],
'prtelno' => $data['prtelno'],
'mobilephone' => $data['mobilephone'],
'emptelefax' => $data['emptelefax'],
'email' => $data['email'],
'promo' => $data['promo'],
'nkfullname' => $data['nkfullname'],
'FK_mscRelation_NK' => $data['FK_mscRelation_NK'],
'nktelefax' => $data['nktelefax'],
'civilstatus' => $data['civilstatus'],
'otherallergies' => $data['otherallergies'],
'pastmedallergy' => $data['pastmedallergy']
]);
}
catch(Exception $ex)
{
return $ex;
}
return $data;
Error text: http://ift.tt/1IFxIlp
Table structure
[id] [int] IDENTITY(1,1) NOT NULL,
[userId] [int] NULL,
[fullname2] [varchar](max) NULL,
[praddress] [varchar](max) NULL,
[occupation] [varchar](max) NULL,
[gender] [varchar](max) NULL,
[prtelno] [varchar](max) NULL,
[mobilephone] [varchar](max) NULL,
[emptelefax] [varchar](max) NULL,
[email] [varchar](max) NULL,
[promo] [varchar](max) NULL,
[nkfullname] [varchar](max) NULL,
[nktelefax] [varchar](max) NULL,
[civilstatus] [varchar](max) NULL,
[pastmedallergy] [varchar](max) NULL,
[otherallergies] [text] NULL,
SQL script won't auto commit
Just want to know if the following is the correct way to write SQL script that uses BEGIN TRANSACTION? (something that works like DML)
BEGIN TRY
BEGIN TRANSACTION
/* SQL statements here */
COMMIT TRANSACTION
END TRY
BEGIN CATCH
IF @@ERROR <> 0
ROLLBACK TRANSACTION
END CATCH
Whenever i tried execute it in SQL server, the script ran but somehow it won't commit the transaction. I still have to manually commit with the COMMIT TRANSACTION.
In my SQL Server Management Studio 2012, i did checked the SET_IMPLICIT_TRANSACTIONS to avoid autocommit when developing the script. But since i already put the statement COMMIT TRANSACTION if all statements ran successfully, then by right my script should autocommit right? Unless if i got mistaken here.
Split Number string into temp table in SQL server
I want to split string like this '1,2,3,4,5,6,7,8,9,10,11,12'
I found code in Internet and I modifier to my work
DECLARE @inpMonth NVARCHAR(MAX) = '1,2,3,4,5,6,7,8,9,10,11,12'
DECLARE @lastYear INT = 2010
DECLARE @delimeter NVARCHAR(1) = ','
DECLARE @mname NVARCHAR(30)
CREATE TABLE #tmp2 (label INT,yy NVARCHAR(100),fromMonth INT,toMonth INT,link INT,mName NVARCHAR(30))
WHILE LEN(@inpMonth) > 0
BEGIN
DECLARE @TYear NVARCHAR(100)
DECLARE @chidx BIGINT = CHARINDEX(@delimeter,@inpMonth)
IF CHARINDEX(@delimeter,@inpMonth) > 0
BEGIN
SET @TYear = SUBSTRING(@inpMonth,0,CHARINDEX(@delimeter,@inpMonth,0))
END
ELSE
BEGIN
SET @TYear = @inpMonth
SET @inpMonth = ''
END
SET @mname = CASE @TYear
WHEN '1' THEN 'Jan'
WHEN '2' THEN 'Feb'
WHEN '3' THEN 'Mar'
WHEN '4' THEN 'Apr'
WHEN '5' THEN 'May'
WHEN '6' THEN 'Jun'
WHEN '7' THEN 'Jul'
WHEN '8' THEN 'Aug'
WHEN '9' THEN 'Sep'
WHEN '10' THEN 'Oct'
WHEN '11' THEN 'Sep'
WHEN '12' THEN 'Dec'
END
INSERT INTO #tmp2 VALUES (@lastYear + 543, @lastYear,@TYear,@TYear, 1,@mname)
SET @inpMonth = REPLACE(@inpMonth,@TYear + @delimeter , '')
END
SELECT *
FROM #tmp2
DROP TABLE #tmp2
Then when I run this code I get record 112 instead 11 and 12 because at end of while statement
REPLACE() are replace '1,' (in first loop) in @inpMonth Like this
REPLACE('1,2,3,4,5,6,7,8,9,10,11,12','1,','')
I get this Result 2,3,4,5,6,7,8,9,10,112
How to I get correct like this '2,3,4,5,6,7,8,9,10,11,12'
Thank you !
How to save image in SQL image type column from picturebox without mentioning pics url?
I have a picturebox where custom image is drawn when mouse button is pressed now i want to save that image in SQL's image type column .
I did search for saving but theres nothing for saving a pic from picturebox to sql image type column without url .
How do I write a Stored Procedure to create a cart in a database?
Hi i want to eventually write code so that a user can add items to a shopping cart on my music website. Users should be able to add/delete items from their cart while logged in, the cart should not clear (even if user logs out) until the an order is placed. The cart table has a primary key set on (CartID, CustID)
From what I understand: 1st I want to create a cart for the particular user, This is the procedure I wrote to create cart, but i'm not sure if this is how I should do it, do I need to check if cart already exists? Is this a good way to create a cart?:
CREATE PROCEDURE usp_Cart_create_cart
-- Add the parameters for the stored procedure here
(
@custID float
)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
insert into Cart (CartID, CustID)
values (+1, @custID)
END
Separate question: Can I create cart and add to cart all in one procedure?
SQL update row depending on other values in same row
I have a table similar to this:
Index Name Type
--------------------------------
1 'Apple' 'Fruit'
2 'Carrot' 'Vegetable'
3 'Orange' 'Fruit'
3 'Mango' 'Fruit'
4 'Potato' 'Vegetable'
and would like to change it to this:
Index Name Type
--------------------------------
1 'Apple' 'Fruit 1'
2 'Carrot' 'Vegetable 1'
3 'Orange' 'Fruit 2'
3 'Mango' 'Fruit 3'
4 'Potato' 'Vegetable 2'
Any chance to do this in a smart update query (= without cursors)?
Create Asymmetric key in SQL using a variable password (from stored procedure)
create procedure SP_INS_PUBLIC_NHANVIEN
@manv varchar(20),
@hoten nvarchar(100),
@email varchar(20),
@luongcb varbinary ,
@tendn nvarchar(100),
@mk varchar
as
create asymmetric key mahoaluongi
with algorithm = RSA_512
encryption by password = @mk
insert into nhanvien values (@manv,@hoten,@email,ENCRYPTBYASYMKEY(mahoaluongi,@luongcb),@tendn,HASHBYTES('SHA1',@mk),@manv)
drop asymmetric key mahoaluongi
go
I want to create a new asymmetric key with new password each time, passed from the parameter of stored procedure. The issue is : "incorrect syntax near @mk. expecting STRING or TEXT_LEX." but it was impossible to convert from varchar to STRING using "cast" or "convert" I'm using SQL server 2014
SQL Syntax Error When Importing .sql file from mysql 4.0
I am trying to import a .sql file that was created with mysql 4.0 into the latest version of mysql and have received a syntax error regarding the following code:
CREATE TABLE edgemap (
senderid int(10) unsigned default NULL,
recipientid int(10) unsigned default NULL,
messageid int(10) unsigned default NULL,
messagedt timestamp(14) NOT NULL,
reciptype enum('bcc','cc','to') default NULL,
subject varchar(255) default NULL,
KEY senderid (senderid,recipientid),
KEY messageid (messageid),
KEY messagedt (messagedt),
KEY senderid_2 (senderid),
KEY recipientid (recipientid)
) ENGINE=MyISAM;
The error message I receive is:
ERROR 1064 (42000) at line 255752: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(14) NOT NULL,
reciptype enum('bcc','cc','to') default NULL,
subject varchar' at line 5
Any help would be much appreciated!
SQL Server Active Directory login groups with limitation
I'm DBA in my organization and use SQL Server 2012 and 2014. I requestd to define multiple group in active directory such as Developer, DBA, supporter and ets... and add my users to this groups. after this a create login from this groups and grant access to DBA, Developer and supporter groups. Change in my organization chart is above, and each time one personnel changed his position, I request to change his group in Active Directory.
My problem: The Admin of Active Directory can add his user or any user to each of this groups and get access to my database without that I get it.
I want to use this groups for grant permission and looked for a way for solve my problem.
Thanks in advance.
PHP MySql MsSql How to Insert or Update a ['] or ["] or [`] Char?
Is there anyway to insert values like ' or " or ` into Mysql or MsSql via php and VB.NET?
It always return an error. Sometimes we must insert these character. In the Phpmyadmin, it can inserts/updated with successfull. But how?
Please help, i am newbie. I cannot search it in MySql/MsSql Dev pages. Thanks
The best way to retrieve large amounts of data from a SQL Server (or SQL Azure) database
I have a table with 40 millions rows. I'm looking for the best technology for retrieving this data to JSON via API.
The performance is critical!
I think, the best way is to use ADO.NET and a SqlDataReader. But, I want to use automapping (like EF).
Could you tell me that is the best way to retrieve large amounts of data? (2, 3,.. 40 million rows)
SQL Server 2012: Select literal and compare to the literal in Where
So, basically, in my query, I want to have a statuses column that will contain 1 or more statuses based on certain criteria. A user should be able to filter/search also on those status messages in that column. I'm not sure on the syntax to make this happen.
This selects 0 to 2 status divs in my column, which I display as is.
Here's theoretically what I want to happen:
Select fields, (select CASE WHEN root_directory IS NULL THEN '<div class="gray status">Unbuilt</div>' ELSE '' END + ' ' + (select top(1) CASE WHEN isnull(payment_received,0) = 1 THEN '' ELSE '<div class="red status">Unpaid</div>' END
FROM invoice C
WHERE C.id = B.id) as statuses
FROM table
WHERE statuses LIKE '%Unbuilt%'
Thoughts on how to WHERE LIKE my statuses column?
Thanks.
Delphi - How to show same column in dbgrid with different criteria
i'm Robby from indonesia, i need your help to finish my delphi homework.
I use ms access database and show all data in 1 dbgrid using sql. I want to show same column but with criteria (50 record per column) i want select query to produce output like,
No | Name | No | Name |
1 | A | 51 | AA |
2 | B | 52 | BB |
3~50 | | 53~100| |
Is it possible ?
Can't get desired output while looping using VB.Net and SQL server
I have the following code, in which I'm trying to loop through a database, extract apartment numbers and meter readings for their heat outputs during a certain year entered by the user.
The output has to be like...
Apartment No. {apartmentNo} - {TotalReading}
Apartment No. {apartmentNo} - {Totalreading} 'second apartment number and so on
The query seems to read only the last data and not the previous ones.
This is my VB.Net code.
Imports System.Data.Sql
Imports System.Data.SqlClient
Public Class AssociationHeating
Private Sub GetAssociationButton_Click(sender As Object, e As EventArgs) Handles GetAssociationButton.Click
Dim con As New SqlConnection
Dim cmd As New SqlCommand
Dim reader As SqlDataReader
Dim totalHeating As Integer = 0
Dim apt_no As Integer = 1
con.ConnectionString = "Server=10.176.165.29,1433;Database=EnergyDB;User=Clerk;Pwd=12345"
cmd.Connection = con
con.Open()
cmd.CommandText = "SELECT apartment_no,reading FROM HeatMeasurement WHERE association_no = '" & AssociationField.Text
& "' and year(reading_date) = '" & YearField.Text & "'"
reader = cmd.ExecuteReader
If reader.HasRows Then
Do While reader.Read()
If Convert.ToInt32(reader.GetString(0)) = apt_no Then
totalHeating += reader.GetInt32(1)
'Console.WriteLine("Read data from apartment: " & reader.GetString(0))
Else
Console.WriteLine("Apartment " & apt_no & " totals: " & totalHeating)
apt_no += 1
Console.WriteLine("Incremented apt_no by one")
totalHeating = 0
End If
Loop
Console.WriteLine("Apartment " & apt_no & " totals: " & totalHeating)
Else
Console.WriteLine("No data found for the year " & YearField.Text)
End If
con.Close()
End Sub
End Class
The printouts look like this: Apartment 1 totals: 110 Incremented apt_no by one Apartment 2 totals: 0
(There are 2 apartments with meters)
Dynamic creation of fields from varying xml structure in sql server 2012
One of the columns in my log table is of type xml, the xml field stores parameters passed to different stored procedures executed in database. So each xml has got a different format and only the root node stays the same. I want to create a table to copy the data values in xml along with other fields in log table(basically expand each row). Any ideas on how to implement this in sql server 2012?
Capturing mutliple XML strings with the same node names in SQL
Weaving my way through the XML string world - I've come across this issue I'm having.
So I have two XML string that are super similar to each other - only thing is - is that they have different info inside the nodes.
XML string 1:
<DocumentElement>
<Readings>
<ReadingID>1</ReadingID>
<ReadingDate>2013-12-19T00:00:00-05:00</ReadingDate>
<Sys>120</Sys>
<Dia>80</Dia>
<PageNumber>4</PageNumber>
<AddedDate>2015-04-17T19:30:22.2255116-04:00</AddedDate>
<UpdateDate>2015-04-17T19:30:22.2255116-04:00</UpdateDate>
</Readings>
<Readings>
<ReadingID>2</ReadingID>
<ReadingDate>2014-01-10T00:00:00-05:00</ReadingDate>
<Sys>108</Sys>
<Dia>86</Dia>
<PageNumber>8</PageNumber>
<AddedDate>2015-04-17T19:32:08.5121747-04:00</AddedDate>
<UpdateDate>2015-04-17T19:32:08.5121747-04:00</UpdateDate>
</Readings>
</DocumentElement>
XML String 2:
<DocumentElement>
<Readings>
<ReadingID>1</ReadingID>
<ReadingDate>2013-12-20T00:00:00-05:00</ReadingDate>
<Sys>140</Sys>
<Dia>70</Dia>
<PageNumber>10</PageNumber>
<AddedDate>2015-04-17T19:30:22.2255116-04:00</AddedDate>
<UpdateDate>2015-04-17T19:30:22.2255116-04:00</UpdateDate>
</Readings>
</DocumentElement>
Now this is really just an example - I could have an infinite amount of strings just like this that I would want to pull data from. In this case I have two strings and I'm looking to extract all info on <Sys>, <Dia> and <ReadingDate>
I would also like to display this info in a table like this:
Reading Date | Sys | Dia
----------------------------
12/29/2013 | 120 | 80
----------------------------
1/10/2014 | 108 | 86
----------------------------
12/20/2013 | 140 | 70
I am totally unsure how to proceed with this - any and all help is appreciated!
Selecting all info from nodes with the same name
I'm a total newbie when it comes to xml stuff.
So far I have this piece of xml that I want to extract info from, but all the node names are the same (so it just grabs one of them, unless stated otherwise).
It looks something like this:
<DocumentElement>
<Screening>
<ScreeningID>2</ScreeningID>
<ScreeningDate>2011-09-13T00:00:00-04:00</ScreeningDate>
<ScreeningResult>1</ScreeningResult>
<ScreeningResultText>Negative</ScreeningResultText>
<TextResult>0</TextResult>
<TextResultText>Not Tested</TextResultText>
<PageNumber>0</PageNumber>
<AddedDate>2015-05-03T16:06:41.71774-04:00</AddedDate>
<UpdateDate>2015-05-03T16:06:41.71774-04:00</UpdateDate>
</Screening>
<Screening>
<ScreeningID>3</ScreeningID>
<ScreeningDate>2011-09-13T00:00:00-04:00</ScreeningDate>
<ScreeningResult>1</ScreeningResult>
<ScreeningResultText>Negative</ScreeningResultText>
<TextResult>1</TextResult>
<TextResultText>Negative</TextResultText>
<PageNumber>9</PageNumber>
<AddedDate>2015-05-03T16:25:21.2904988-04:00</AddedDate>
<UpdateDate>2015-05-03T16:25:21.2904988-04:00</UpdateDate>
</Screening>
And I'm currently using this kind of snippet to extract info from the TextResult area
Select
answer.value('(/DocumentElement/Screening/TextResult)[1]','int')
From
Answers
However, that only grabs the first bit of info, I know that if I write something like this, it'll get me the second bit of info but on another column: answer.value('(/DocumentElement/Screening[2]/textResult)[1]','int')
I have two issues with this: 1. There isn't necessarily going to be only 2 nodes with the same name - it could go on infinitely. And 2. I would like all the info to be gathered into only one column.
Any help would be appreciated!
How to configure Passportjs with mssql?
I am Writting an app based on Mssql,ExpressJs,NodeJS,AngularJs, I am an express begginer, I need to handle sessions so I have been thinking on PassportJs, I can't find documentation to integrate mssql with PassportJs so I am pretty confused, I have been trying but I don't get it Yet, I have built my app with express-generator so this is my app.js
Passport requires
var passport = require('passport');
var LocalStrategy = require('passport-local').Strategy;
var session = require('express-session');
Routes
var routes = require('./routes/index');
var login = require('./routes/login');
express-session
app.use(session({secret: 'xXxXxXXxX'}));
app.use(passport.initialize());
app.use(passport.session());
app.use('/', routes);
app.use('/login', login);
passport-init
var initPassport = require('./r12_modulos/sesion/passport-init.js');
initPassport(passport);
This is what I have in passport-init.js:
var conex = require('../conexion_bd/conex_mssql.js');
var passport = require('passport');
var LocalStrategy = require('passport-local').Strategy;
module.exports = function () {
passport.serializeUser(function (user, done) {
console.log('serializing user:', user.username);
done(null, user.username);
});
passport.deserializeUser(function (username, done) {
done(null,username);
});
passport.use('login', new LocalStrategy({
passReqToCallback: true
},
function (req, username, password, done) {
var _cParameters = [];
_cParameters.push({ name: 'usuario', type: 'VarChar', value: username });
_cParameters.push({ name: 'password', type: 'NVarChar', value: password });
conex.sp_recordset(conex.mssql_conect, 'dbo.sp_sis_loginR12', _cParameters, function (data) {
if (data[0].response == 1) {
return done(null, data[0].usuario);
}
else {
return done(null, false);
}
});
}
));
};
As you can see I have wrote a module to execute Mssql StoreProcedures, When I am searching on internet passportjs is commonly integrated with Mongo, I don't know how to handle the passport.serializeUser and passport.deserializeUser functions with mssql
This is What I have in the route login.js
var express = require('express');
var passport = require('passport');
var router = express.Router();
router.post('/', passport.authenticate('login', {
successRedirect: '/',
failureRedirect: '/login'
}));
module.exports = router;
when I send a post request the server does not return an error but do not run my LocalStrategy.
SQL server 2008R2 shutting down(0x80041033)
I installed the SQL Server 2008R2, but this error report. system: Windows 7 Please help to look at, thank you ! online etc.
SQL RESTORE WITH RECOVERY; Hangs at 100%
I have done a lot of research about this.
I am attempting to recover a database with SQL Server 2014 and it keeps hanging at 100%.
A lot of people suggest that the solution is to just make sure that you restore with the RECOVERY option.
I have tried that and it still hangs at 100%. I have tried via the SSMS Restore dialog and I have tried running the following SQL Statement:
USE [master]
RESTORE DATABASE [MyDB]
FROM DISK = N'C:\MyDB_backup_2015_05_05_010004_1506557.bak'
WITH
FILE = 1,
MOVE N'MyDB_Data' TO N'F:\MSSQL\DATA\MyDB.mdf',
MOVE N'MyDB_Log' TO N'F:\MSSQL\DATA\MyDB_1.ldf',
NOUNLOAD,
REPLACE,
RECOVERY,
STATS = 2
GO
When I check the status of the command via:
SELECT r.status, r.command, r.wait_type
FROM sys.dm_exec_requests r
WHERE r.command like '%restore%' or r.command like '%backup%'
I get:
status: suspended
command: RESTORE DATABASE
wait_type: BACKUPTHREAD
Which from my reading implies that the RESTORE is waiting for a BACKUP to complete, but there is no BACKUP command returned from my query to sys.dm_exec_requests
So I'm at a loss...
Anyone got any ideas what's going on here or any tips on how to diagnose the issue?
Cheers!
How to use connection string in ODBC to create a table in Access permanently linked to a SQL source?
I have created a table in my Access front end application, and try to connect it to the back end database with the following subroutine:
Sub createFlowTable()
Dim db As Database
Dim tblDef As TableDef
Set db = CurrentDb
Set tblDef = db.CreateTableDef("myTable")
tblDef.Connect = "ODBC;DRIVER=SQL Server;SERVER=myServer; Trusted_Connection=No;UID=<myUID>;PWD=<myPWD>;APP=2007 Microsoft Office system;DATABASE=myDataBase;Network=DBMSSOCN;TABLE=dbo.myTable"
tblDef.SourceTableName = "mySourceTableName"
db.TableDefs.Append tblDef
End Sub
After I close the front end Access database, and upon reopening it, the table fails to open. Even though I have set the Trusted_Connection to "No" in my string, the table still tries to use the Windows Authentication. Also, when I open the table on design view, I see in front of "Description":
ODBC;DRIVER=SQL Server;Server=myServer;APP=2007 Microsoft Office System;DATABASE=myDatabase;Network=DBMSSOCN;Table=dbo.myTable
So obviously Access has not saved the UID and PWD, nor has it saved the instruction on setting the Trusted_Connection to "No".
I insist to get this done with the connection string, and using DSN will not work for the purpose of my application. Help would be greatly appreciated.
.Net - Serializable issue with List with Session stored in SQL Server
We are using .Net 4.0 and SQL Server for our Session Store.
We have marked all our objects as [Serializable] but we are still getting the error:
System.Web.HttpException (0x80004005): Unable to serialize the session state.
In turns out the issue is with a call to a ToList method we have implemented
if (licenseHoldings != null && licenseHoldings.**ToList()**.Any())
And the implementation of LicenseHoldings contains this method
public List<License> ToList()
{
var list = new List<License>();
if (SerializableObject != null)
list.Add(SerializableObject);
return list;
}
Appreciate any guidance on why a List of a serializable object is not serializable to a SQL Server session and on an alternative implementation to this method.
Duplications in Query Result
I am working on SQL Server to organize the generated data in C#. However, when I check the data in the table, there is a problem.
Here is the sample results:
Title Author Names with w/o
Estimating the usefulness ... W Meng KL Liu C Yu W Wu N Rishe 71 64
Estimating the usefulness ... W Meng KL Liu C Yu W Wu N Rishe 71 58
Estimating the usefulness ... W Meng KL Liu C Yu W Wu N Rishe 71 54
Estimating the usefulness ... W Meng KL Liu C Yu W Wu N Rishe 71 53
The effect of negation ... L Jia C Yu W Meng 66 65
The effect of negation ... L Jia C Yu W Meng 66 65
The effect of negation ... L Jia C Yu W Meng 66 65
The effect of negation ... L Jia C Yu W Meng 66 65
What kind of query should I write to get this result:
Estimating the usefulness ... W Meng KL Liu C Yu W Wu N Rishe 71 53
The effect of negation ... L Jia C Yu W Meng 66 65
Thank you for your time and help.
Note: Distinct does not work for this.
MS SQL Server Report Builder_Concatenate data type "date" and "time"
I am working on MS SQL Server Report Builder 2008 r.
I have a Dataset contains DateEntry (date,null) and TimeStampAuto (time(7),null). I am trying to write an expression for concatenating those two values, so I can put it under DATETIME column on the report table. I tried the following but it does not work. It displays "#Error."
=First(Fields!DateEntry.Value, "Report1) & " " & First(Fields!TimeStampAuto.Value, "Report1)
When I just put the first part of the expression shown above, the report displays the date with some random time value (5/1/2015 12:00:00 AM). However, I did not put any time value on DateEntry. I only put the dates. When I put the second part of the expression shown above, the report displays correct time that the data has.
I don't know why I can not concatenate those two.
How to get all columns that are used from various tables?
Lets say I have 10 tables (Table1 - Table10) and 10 columns in each table (lets say Column1 - Column10) - same column names across tables. I wanna find all columns in all tables that are not NULL for at least one row in that table and insert them into temp table for example @usedColumns that will be used later in the stored procedure.
What would be the fastest and most efficient way to do it assuming that you have millions of records in any or all of these tables? Current implementation uses UNPIVOT, but I'm trying to redesign that stored procedure since it's acting really slow. Or is UNPIVOT really the most efficient way?
One way I was thinking is to just check for if exists, but can't figure out how to put this into an efficient loop:
if exists (select top 1 1 from Table1 where Column1 is not null)
insert into @usedColumns(table_name, column_name)
values('Table1', 'Column1');
if exists (select top 1 1 from Table1 where Column2 is not null)
insert into @usedColumns(table_name, column_name)
values('Table1', 'Column2');
...
The DB in question is SQL Server, but I'm guessing the solution would work in MySQL and others too.
Identify foreign key relationships without contstraints
We have a legacy SQLServer database with few specified foreign key constraints, even though there are many foreign keys. Is there some way to use the server logs to identify the foreign keys even though they are not specified?
SQL Server triggers - Update one field one time once another isn't null
I'm fairly new to triggers and I'm trying to update Field1 if it's null with the value from Field2 as long as Field2 isn't null on an update or insert. In other words, I want to retain the first non null value from "NonAccrual" and store it in "OriginalNonAccrual". Once OriginalNonAccrual gets a value, it should remain the same indefinitely even if NonAccrual changes. I don't know if one trigger can handle both an insert and an update and hopefully the code below gives you the idea of what I'm trying to do. Thank you!
CREATE TRIGGER tr_SAG_Accrual
ON [dbo].[SAG]
AFTER INSERT, UPDATE
AS
BEGIN
Declare @NonAccrual date
Declare @OriginalNonAccrual date
Select @NonAccrual = NONACCRUAL_DATE
, @OriginalNonAccrual = OriginalNonAccrualDate
from inserted
IF @OriginalNonAccrual IS NULL AND NOT @NonAccrual IS NULL
SET @OriginalNonAccrual = @NonAccrual
END
Which DataType should be used for Editor & File and image browser
Which datatype should be used in MSSQL database with an MVC application for the following fields as on the following components? http://ift.tt/1chOLhe
- Editor & File
- Image browser
invalid stream header during java byte deserialization from sql server
I need to save in SQL Server 2008 table a serialized Object Stream and then deserialize it. The problem arise when i deserialize..I get the following Exception:
Exception in thread "main" java.io.StreamCorruptedException: invalid stream header: 5B424065
at java.io.ObjectInputStream.readStreamHeader(ObjectInputStream.java:804)
at java.io.ObjectInputStream.<init>(ObjectInputStream.java:299)
I use JTDS-1.2.4 (not the last JTDS Driver Type 4)
In table i save in a column type -> NVARCHAR(MAX), i have this value for e.g.
[B@e3fd79
i read the value above (jtds give me a sql.Clob) and i try to deserialize it
My Java Code:
DocumentObjectHolder doc = new DocumentObjectHolder(xmldata, "data.xml", TYPE.XML, xmldata.getBytes("UTF-8"));
//SERIALIZE DocumentObjectHolder
ByteArrayOutputStream bof = new ByteArrayOutputStream();
ObjectOutputStream serialize = new ObjectOutputStream(bof);
serialize.writeObject(doc);
SQLDbManagerFactory.setDbConnectionParameters(dbUri, username, password, driver);
SQLDBManager factoryDb = SQLDbManagerFactory.getSQLDBManager();
factoryDb.execSQL("INSERT INTO MY_DOCUMENTS (DATA,DOCUMENT_TYPE,IS_READY,DO_EMIT,IS_EMITTED)" +
" VALUES ( '" + bof.toByteArray() + "','" + TYPE.XML.name() + "', 0, 0, 0)");
RecordSet rs = (RecordSet) factoryDb.execSQL("SELECT TOP 1 DATA FROM MY_DOCUMENTS");
if (rs != null && rs.getLength() > 0){
//DESERIALIZE in DocumentObjectHolder
Clob objris = (Clob)rs.get(0, 0);
InputStream in = objris.getAsciiStream();
byte[] b = new byte[in.available()];
in.read(b);
ByteArrayInputStream bais = new ByteArrayInputStream(b);
ObjectInputStream ins = new ObjectInputStream(bais);
DocumentObjectHolder mc =(DocumentObjectHolder)ins.readObject();
System.out.println("Object in value ::"+mc.toString());
ins.close();
in.close();
}
SQLDBManager is my private library ..
I suppose it would be a Blob (byte blob) not a Clob (char lob), so i tried to change nvarchar(max) to varbinary(500) because i read here: http://ift.tt/1OYz7Zi
but i get the following exception:
Exception in thread "main" java.sql.SQLException: Invalid SQL statement or JDBC escape, terminating ']' not found.
at net.sourceforge.jtds.jdbc.SQLParser.parse(SQLParser.java:1155)
at net.sourceforge.jtds.jdbc.SQLParser.parse(SQLParser.java:156)
at net.sourceforge.jtds.jdbc.JtdsPreparedStatement.<init>(JtdsPreparedStatement.java:107)
at net.sourceforge.jtds.jdbc.ConnectionJDBC2.prepareStatement(ConnectionJDBC2.java:2456)
at net.sourceforge.jtds.jdbc.ConnectionJDBC2.prepareStatement(ConnectionJDBC2.java:2414)
What's wrong?
Use a Sequence in a function Sql Server
Im trying to use a sequence inside a sql function but T-Sql dosent allow the use of "New Value for", and "OPENROWSET" dosent allow me to do querys because of the security of the database.
CREATE FUNCTION dbo.newInvoice()
RETURNS varchar
AS
BEGIN
DECLARE @NumberHold AS bigint
SELECT @NumberHold = NEXT VALUE FOR dbo.Ten_Seq
RETURN dbo.sfnSprintf('MyOmnyInvoice[%s]', CAST(@NumberHold as varchar),default);
END
Use a trigger in SQL Server 2008 to change the value of one column based on the value of another column during update
I need to change the value of a column when a record is updated, based on the value of another column in the same table. The legacy application updating the table cannot be re-coded to handle this.
The basic logic would be:
If DateShipped is not null, set OrderLocation = 4
Hoping I can do this at the database level with an update trigger in MS SQL Server 2008 r2.
thank you.
Is there an SQL Server equivalent function to Progress 4GL INTERVAL function ?
I am aware that Progress 4GL version 10 onwards is equipped with an very clever integrated function called 'INTERVAL' which returns the time interval between two DATE, DATETIME, or DATETIME-TZ values. If you use the following syntax,
> DISPLAY INTERVAL(05/06/2014, 05/05/2015, "years").
would give you zero as it is one day short for it to be counted as one year.
But in SQL, the closet function can be used in relation to this would be DATEDIFF and you would not be able to get the same result. In SQL Server the following query,
PRINT DATEDIFF(YEAR, '05/06/2014', '05/05/2015');
would still print one instead of zero ignoring the bit that it is still one day short to be counted as a year. Therefore my question is would there be an integrated function in SQL which could be used to achieve the same result like Progress 4GL ?
Inscription à :
Commentaires (Atom)