Click here to Skip to main content
14,693,599 members
Home » Discussions » Database
   

Database

 
GeneralRe: Fatal error: Out of memory (allocated 408944640) (tried to allocate 805306376 bytes) Pin
jschell29-Nov-17 12:44
Memberjschell29-Nov-17 12:44 
AnswerRe: Fatal error: Out of memory (allocated 408944640) (tried to allocate 805306376 bytes) Pin
Jochen Arndt28-Nov-17 0:25
professionalJochen Arndt28-Nov-17 0:25 
QuestionI need to install Sql server to write some practice C# applications with small Database Pin
indian14324-Nov-17 7:02
Memberindian14324-Nov-17 7:02 
AnswerRe: I need to install Sql server to write some practice C# applications with small Database Pin
Eddy Vluggen24-Nov-17 7:08
professionalEddy Vluggen24-Nov-17 7:08 
GeneralRe: I need to install Sql server to write some practice C# applications with small Database Pin
PIEBALDconsult24-Nov-17 7:51
professionalPIEBALDconsult24-Nov-17 7:51 
AnswerRe: I need to install Sql server to write some practice C# applications with small Database Pin
Victor Nijegorodov24-Nov-17 11:18
MemberVictor Nijegorodov24-Nov-17 11:18 
AnswerRe: I need to install Sql server to write some practice C# applications with small Database Pin
Richard Deeming28-Nov-17 3:42
mveRichard Deeming28-Nov-17 3:42 
QuestionTrggier not working with insert Pin
issam mansour22-Nov-17 23:39
Memberissam mansour22-Nov-17 23:39 
Hi,
I have an insert command saved to a table in a database "after insert" the trigger take the insert command and execute it. when the trigger execute it save the tweet in a second table on the in the same database and it work fine but the trigger in the second table does not work at all. following are the trigger and the table's:-

Command Insert
SET QUOTED_IDENTIFIER OFF 
SET ANSI_NULLS ON
INSERT INTO [jbmolver].[dbo].[all_tweets] ([set_str],[name]) VALUES 
("INSERT INTO [jbmolver].[dbo].[Twitter_Feeds] ([Twitter_ID],[Feed_ID],[Twitter_Date],[screen_name],[Author_Details],[Author_Email],[Author],[Sentiment],[sdate],[edate],[keyword],[type],[ncount],[latitude],[longitude],[radius],[lang],[friends_count],[followers_count],[following],[statuses_count],[location],[time_zone],[description],[in_reply_to_status_id],[in_reply_to_user_id],[in_reply_to_screen_name],[Rcounter],[address],[Feed_From_SM],[profile_image],[current_user_retweet],[withheld_scope],[retweet_count],[favourites_count],[url_type],[hashtags],[expanded_url]) VALUES ('933231091967778816','799796908478894080','2017-11-22 09:10:05',N'prettyraebae',N'Sis it s almost 2018 😭 https://twitter.com/babydrii_/status/933230970412683264', '', 'Rae', '1', '', '', '', 'photo', '', '', '', '', 'en', '461', '737', '', '12503', '', '', N'UGA 20 ❤️', '', '', '', '0', '$', '2', 'http://pbs.twimg.com/profile_images/867998936388313089/2Zdl5P2-_normal.jpg', '0', '0', '0', '27988', '', '', '')", 'Twitter_Feeds')

Table No. One:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[all_tweets](
[id] [int] IDENTITY(1,1) NOT NULL,
[set_str] [nvarchar](max) NULL,
[name] [nchar](30) NULL,
CONSTRAINT [PK_all_tweets] PRIMARY KEY CLUSTERED 
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

Trigger on Table One:

/****** Object: Trigger [dbo].[exec_Update] Script Date: 11/23/2017 5:09:00 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[exec_Update] on [dbo].[all_tweets]
AFTER INSERT
AS 
BEGIN
declare @id int = 0
set @id = ( select top 1 id from inserted )
exec [dbo].[execute_all_tweets] '[dbo].[all_tweets]','[set_str]',@id
END

Table Number Two
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Twitter_Feeds](
[id] [int] IDENTITY(1,1) NOT NULL,
[Twitter_ID] [bigint] NOT NULL CONSTRAINT [DF_Twitter_Feeds_Twitter_ID] DEFAULT ((0)),
[Feed_ID] [nvarchar](50) NULL,
[Twitter_Feed] [varchar](200) NULL CONSTRAINT [DF__Twitter_F__Twitt__6E01572D] DEFAULT (''),
[Resource] [nchar](50) NULL CONSTRAINT [DF_Twitter_Feeds_Resource] DEFAULT (''),
[Twitter_Date] [datetime] NULL,
[screen_name] [nvarchar](50) NULL CONSTRAINT [DF__Twitter_F__scree__6FE99F9F] DEFAULT (''),
[friends_count] [nvarchar](50) NULL CONSTRAINT [DF__Twitter_F__frien__70DDC3D8] DEFAULT (''),
[followers_count] [nvarchar](50) NULL CONSTRAINT [DF__Twitter_F__follo__71D1E811] DEFAULT (''),
[following] [nvarchar](50) NULL,
[statuses_count] [nvarchar](50) NULL,
[description] [nvarchar](max) NULL CONSTRAINT [DF__Twitter_F__descr__72C60C4A] DEFAULT (' '),
[Author_Details] [nvarchar](max) NULL CONSTRAINT [DF_Twitter_Feeds_Author_Details] DEFAULT (''),
[Author_Email] [varchar](100) NULL CONSTRAINT [DF__Twitter_F__Autho__74AE54BC] DEFAULT (''),
[Author] [nvarchar](100) NULL CONSTRAINT [DF__Twitter_F__Autho__75A278F5] DEFAULT (''),
[Sentiment] [int] NULL CONSTRAINT [DF__Twitter_F__Senti__76969D2E] DEFAULT ((0)),
[sdate] [date] NULL,
[edate] [date] NULL,
[keyword] [nvarchar](250) NULL CONSTRAINT [DF__Twitter_F__keywo__778AC167] DEFAULT (' '),
[type] [nvarchar](50) NULL CONSTRAINT [DF_Twitter_Feeds_type] DEFAULT (' '),
[ncount] [int] NULL,
[latitude] [nvarchar](50) NULL CONSTRAINT [DF__Twitter_F__latit__787EE5A0] DEFAULT (''),
[longitude] [nvarchar](50) NULL CONSTRAINT [DF__Twitter_F__longi__797309D9] DEFAULT (''),
[radius] [nvarchar](50) NULL CONSTRAINT [DF_Twitter_Feeds_radius] DEFAULT ((0)),
[lang] [nvarchar](5) NULL,
[in_reply_to_status_id] [nvarchar](50) NOT NULL CONSTRAINT [DF_Twitter_Feeds_in_reply_to_status_id] DEFAULT ((0)),
[in_reply_to_user_id] [nvarchar](50) NOT NULL CONSTRAINT [DF_Twitter_Feeds_in_reply_to_user_id] DEFAULT ((0)),
[in_reply_to_screen_name] [nvarchar](50) NULL,
[Rcounter] [int] NULL CONSTRAINT [DF_Twitter_Feeds_Rcounter] DEFAULT ((0)),
[country] [nvarchar](100) NULL,
[State] [nvarchar](50) NULL,
[City] [nvarchar](50) NULL,
[address] [varchar](250) NOT NULL CONSTRAINT [DF_Twitter_Feeds_address_1] DEFAULT (''),
[location] [nvarchar](50) NULL,
[time_zone] [nvarchar](50) NULL CONSTRAINT [DF_Twitter_Feeds_time_zone] DEFAULT (' '),
[Feed_From_SM] [int] NOT NULL CONSTRAINT [DF_Twitter_Feeds_Feed_From_SM] DEFAULT ((1)),
[gender] [int] NOT NULL CONSTRAINT [DF_Twitter_Feeds_gender] DEFAULT ((0)),
[Client_id] [int] NOT NULL CONSTRAINT [DF_Twitter_Feeds_Client_id] DEFAULT ((0)),
[image] [image] NULL,
[profile_image] [nvarchar](180) NULL,
[withheld_in_countries] [nchar](50) NULL CONSTRAINT [DF_Twitter_Feeds_withheld_in_countries] DEFAULT (''),
[withheld_scope] [nchar](10) NULL CONSTRAINT [DF_Twitter_Feeds_withheld_scope] DEFAULT (''),
[retweet_count] [int] NULL CONSTRAINT [DF_Twitter_Feeds_retweet_count] DEFAULT ((0)),
[current_user_retweet] [int] NULL,
[favourites_count] [int] NULL,
[expanded_url] [nvarchar](max) NULL,
[url_type] [nchar](150) NULL,
[hashtags] [nchar](150) NULL,
CONSTRAINT [PK_Twitter_Feeds_2] PRIMARY KEY CLUSTERED 
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO

Trigger On table number two
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[wordlist_splite_update] on [dbo].[Twitter_Feeds]
AFTER INSERT
AS 
BEGIN
--set nocount on
DECLARE @MyString NVARCHAR(100),@TABLENAME NVARCHAR(50)='[dbo].[twitter_feeds]',@FIELD NVARCHAR(50)='[author_details]',@min int = 4
SET @MyString = ','
declare @Country nvarchar(80),@State nvarchar(50),@City nvarchar(50),@y int, @i int
declare @w1 Nvarchar(256),@w2 Nvarchar(256),@w3 Nvarchar(256),@w4 Nvarchar(256),@fld Nvarchar(256),@ID INT,@Twitter_Date datetime

set @fld = (select top 1 keyword from inserted)
set @Twitter_Date = (select top 1 Twitter_Date from inserted)
set @id = (select top 1 id from inserted)
SET TEXTSIZE 2147483647;
--exec [dbo].[top_word_list_by_location] '[twitter_feeds]','[author_details]', @id 

IF OBJECT_ID('dbo.Cur1', 'U') IS NOT NULL 
DROP TABLE dbo.Cur1;
IF OBJECT_ID('dbo.sometable', 'U') IS NOT NULL 
DROP TABLE dbo.sometable; 

--SET NOCOUNT ON
DECLARE @Words INT, @Pos INT, @x Int, @str varchar(256),@screen_name nvarchar(100),@Author_Details nvarchar(280)
, @word varchar(256), @start int, @end int, @exitstart int
SELECT @Words = 0, @Pos = 1, @x = -1, @Word = '', @start = 1

if @id > 0
begin
exec('DECLARE Cur1 CURSOR FOR SELECT id, [screen_name],('+@FIELD+'),Twitter_Date FROM '+@TABLENAME +'where id='+@id )
end
else
begin
exec('DECLARE Cur1 CURSOR FOR SELECT id, [screen_name],('+@FIELD+'),Twitter_Date FROM '+@TABLENAME)
end


OPEN Cur1

fetch next from cur1 into @id,@screen_name,@str,@twitter_date
--insert into [dbo].[wordlist] ([word]) values (@id)

while @@fetch_status = 0
begin
select * into #temp from [dbo].[splitwords](@str)
set @y = (select count(*) from #temp)
set @i = 1

while @i <= @y
begin
--insert into [dbo].[wordlist] ([word]) values (@i)
set @word = (select [value] from #temp where id = @i)
set @word = (select [dbo].[udf_parsealphachars](@word))
if not exists (select * from dbo.dictionary with(nolock) where word= ltrim(rtrim(@word)))
begin
if not exists(select * from [dbo].[wordlist] with(nolock) where (word=ltrim(rtrim(@word)) and used_day = day(@twitter_date) and used_month = month(@twitter_date) and used_year = year(@twitter_date)) and (ltrim(rtrim([screen_name])) = ltrim(rtrim(@screen_name)) and nullif(word,' ') is not null))
begin
if len(ltrim(rtrim(isnull(@word,'')))) >=0 -- @min
begin

insert into [dbo].[wordlist] ([word],[used_day],[used_month],[used_year],[screen_name],[add_dt])
(select ltrim(rtrim(@word)),day(@twitter_date),month(@twitter_date),year(@twitter_date),@screen_name,@twitter_date where (len(isnull(@word,'')) > @min))

end ---- if len(ltrim(rtrim(isnull(@word,''))))

end --- if not exists [dbo].[wordlist]
else
begin
update wordlist set wordcount = wordcount + 1,[lastupdate] = getdate() where word = ltrim(rtrim(@word)) and (len(isnull(ltrim(rtrim(@word)),'')) > @min) and (ltrim(rtrim(@word)) <> '-1')
end --- else [dbo].[wordlist]
end --- if not exists [dbo].[wordlist]

set @i = @i + 1
end ---- end while dbo.dictionary

fetch next from cur1 into @id,@screen_name,@str,@twitter_date
end ---- end fetch next 


close cur1
deallocate cur1
--set nocount off

--exec('select top '+ @top +' * from wordlist order by wordid desc')

if object_id('dbo.cur1', 'u') is not null 
drop table dbo.cur1;
if object_id('dbo.#temp', 'u') is not null 
drop table dbo.#temp; 
--set nocount off
end 

AnswerRe: Trggier not working with insert Pin
Richard Deeming23-Nov-17 2:03
mveRichard Deeming23-Nov-17 2:03 
Questiondatabase log file configuration.. Pin
Member 1353603022-Nov-17 9:31
MemberMember 1353603022-Nov-17 9:31 
AnswerRe: database log file configuration.. Pin
Richard MacCutchan22-Nov-17 22:46
mveRichard MacCutchan22-Nov-17 22:46 
GeneralRe: database log file configuration.. Pin
Member 1353603027-Nov-17 8:12
MemberMember 1353603027-Nov-17 8:12 
AnswerRe: database log file configuration.. Pin
Eddy Vluggen23-Nov-17 2:35
professionalEddy Vluggen23-Nov-17 2:35 
GeneralRe: database log file configuration.. Pin
Member 1353603027-Nov-17 8:11
MemberMember 1353603027-Nov-17 8:11 
GeneralRe: database log file configuration.. Pin
Eddy Vluggen27-Nov-17 8:55
professionalEddy Vluggen27-Nov-17 8:55 
GeneralRe: database log file configuration.. Pin
Member 1353603027-Nov-17 9:12
MemberMember 1353603027-Nov-17 9:12 
GeneralRe: database log file configuration.. Pin
Eddy Vluggen27-Nov-17 10:54
professionalEddy Vluggen27-Nov-17 10:54 
AnswerRe: database log file configuration.. Pin
jschell27-Nov-17 11:10
Memberjschell27-Nov-17 11:10 
QuestionEntity Framework VS Linq To SQL Pin
Kevin Marois15-Nov-17 13:29
professionalKevin Marois15-Nov-17 13:29 
AnswerRe: Entity Framework VS Linq To SQL Pin
Richard Deeming28-Nov-17 8:17
mveRichard Deeming28-Nov-17 8:17 
AnswerRe: Entity Framework VS Linq To SQL Pin
Nathan Minier28-Nov-17 9:58
professionalNathan Minier28-Nov-17 9:58 
QuestionChange old store to new store Pin
Member 1333807514-Nov-17 19:13
MemberMember 1333807514-Nov-17 19:13 
AnswerRe: Change old store to new store Pin
Richard Deeming15-Nov-17 3:47
mveRichard Deeming15-Nov-17 3:47 
AnswerRe: Change old store to new store Pin
CHill6015-Nov-17 11:23
mveCHill6015-Nov-17 11:23 
QuestionSSIS throwing error as: Did not read byte array properly Pin
indian1438-Nov-17 9:14
Memberindian1438-Nov-17 9:14 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.