Hi all,
Is there a difference between the DBA and the SA or are they one in the same. I was reading a previous thead and came across that the SA is the powerful God almighty and the DBA is the just God.
Thanks
Lystra
I have SSRS 2005 SP2 configured to work in Sharepoint integration. Everything works fine except that I am not able to programmatically change any property of report viewer web part (instance) that I have added on on home page of my sharepoint site.
I can do same thing via sharepoint UI but not through program. When my programs runs it fetches all web parts been added on home page, then I need to iterate through each one and find report viewer web part.
While iterating, as soon as I arrive to report viewer web part it is named as "Error web part" with error message as
"Windows SharePoint Services cannot deserialize the Web Part. Check the format of the properties and try again"
If someone has a solution, please respond at your earlist.
Thanks
Shankar
I have a table with a column named measurement decimal(18,1). If the value is 2.0, I want the stored proc to return 2 but if the value is 2.5 I want the stored proc to return 2.5. So if the value after the decimal point is 0, I only want the stored proc to return the integer portion. Is there a sql function that I can use to determine what the fraction part of the decimal value is? In c#, I can use
dr["measurement "].ToString().Split(".".ToCharArray())[1] to see what the value after the decimal is.
I have made the following Scalar-valued function:
CREATE FUNCTION [dbo].[TimeCalc]
(
@OriginalTime AS INTEGER
, @TenthsOrHundredths AS INTEGER -- code 2: 1/10, code 4: 1/100
)
RETURNS NVARCHAR(8)
[Code] ....
What it does is convert numbers to times
E.g.: 81230 gets divided by 10 (times in seconds: 8123). This 1 1 full minute, and the remainder = 2123 making it 1.21.23 mins)
So far so good (function works perfectly)
My question: sometimes times are in 1/100 (like above sample), sometimes in 1/10.
This means that, e.g. with a time like 3.23.40 the last zero must be deleted.
My thoughts are to use the results from the Return Case part, and as the code = 4: leave it as it is,
is the code 2 the use LEFT(... result Return Case ..., Len(..result Return Case.. - 1))
There are 5 codes: 0 1 2 3 and 4
Question is in the subject.
Thanks in advance
-Jamie
What different are there between connectionString (Part 1) and connectionString (Part 2) in web.config
The CCWW is my PC's name, normally I can connect to the database ASPNETDB.MDF correctly either Part 1 or Part 2 in a web page,After I open Database Explorer panel and browse ASPNETDB.MDF, I can't connect to database using Part 2 when I open a webpage in Microsoft Visual Web Developer 2005 Express Edition,but I can correctly open a webpage using Part 1 after I open Database Explorer panel.
What different are there between connectionString (Part 1) and connectionString (Part 2) in web.config?
I guess while I use Part 1 to connect, maybe it will be cancel exclusive method of the database ASPNETDB.MDF first, but when I connect to database using Part 2, maybe two programms both Part 2 and Database Explorer visit ASPNETDB.MDF at the same time!
--------------------------------------Part 1------------------------------------------------------------------------<add name="MyConnect" connectionString="Data Source=.SQLExpress;Integrated Security=True;User Instance=True;AttachDBFilename=|DataDirectory|ASPNETDB.MDF" providerName="System.Data.SqlClient" /> --------------------------------------Part 1------------------------------------------------------------------------
--------------------------------------Part 2------------------------------------------------------------------------<add name="MyConnect" connectionString="Data Source=CCWWSQLEXPRESS;Initial Catalog=ASPNETDB.MDF;Integrated Security=True;User Instance=True;AttachDBFilename=|DataDirectory|ASPNETDB.MDF" providerName="System.Data.SqlClient" />--------------------------------------Part 2------------------------------------------------------------------------
In a recent post, I've read that Jet Provider is only provided with 32-bit.
I wonder, is there any problem if you run a SSIS package 32-bit which read from Access and then call another SSIS package 64 bit?? We've got quite 2000 dts which pulling data from MDB and within a time it'll disappear in favour 2005 environment .
Another question, what sort of Providers don't offer us 64-bit versions???
Any link or thought will be as usual welcomed
Getting back way too many records with this on - over 4500 and should only be about 350.....
Here's the sql that's not working right:
SELECT c.Name, a.ID, a.Description, s.Status, b.Location, a.scheduleBegin, a.scheduleEnd
from tblEquipments a
inner join tblEquipments_Group U on u.categoryID = a.EquipmentType
inner join tblStatus S on a.status = S.statusID
and u.subCategoryID = @type
inner join tblLocation b on a.status = b.locationID
inner join tblSubCategories c on u.subCategoryID = @type
and c.Parent = @type
The table tblSubCategories has a field Name that I need.
tblSubCategories has a field, Parent, that will = @type
See this post for the other table fields if needed.
http://forums.asp.net/thread/1489880.aspx
If I omit the last inner join, all is ok, but I need that last bit of info - the c.Name.
Suggestions?
Thanks,
Zath
ok, following up on my previous post that I marked as answered a little premature. The query below works fine in sql studio:
SELECT id, CONVERT(NVARCHAR(10), arrdate,101) as formatedDate
FROM guest
but when I try to use it in a c# code behind file:
comm = new SqlCommand("SELECT id, CONVERT(NVARCHAR(10), arrdate,101) as formatedDate FROM guest WHERE id = @id", conn);
it bombs??
hi friends..... i want to get part of the database field value in stored procedure.. ex : select reason from reasontable where rid=1 output : reason-------------------------------telephone is not working properly. but i want the output as : reason--------------------------------telephone is n.............
View 3 Replies View RelatedWhich database do you point it at when you're executing the script?
The Master database?
Need help..
I need to select from a text field (lastname, firstname) the first part which is the last name. The format is exactly like the parenthesis. Any ideas?
Thanx
Thanks everyone for being helpful. I am new to SQL Server and don't have many coworkers so I am relying heavily on this forum's help.
It seems the previous poster on this issue was able to work it out but I am still not able to get it to work.
I have tried using the server URL, the server machine name, the ipaddress with alias, but still cannot connect with the replication over vpn. Any ideas?
Also, how can I set the subscriber from the server? How do I refer to the subscriber machine? Let's say it is my pc that I want to make changes in and have them replicate to the server? When I specify a pull subscription I am not show how to refer to my computer.
I have a tabkle that I want to use for NT authnetication. It was a list of domain lan usernames. I want to use NT authentication in ASP but in order to do so I need to add the domain to each of the lan ds. So my username, kssensalo needs to be DOMAINkssensalo. I need to do this for 5,000+ plus records. What SQL fucntion would I use? I know I can use LTRIM to remove, but I need to add or append.
Any suggesttions?
Hi,
i am trying to write a query the pulls out a table which has date information stored as dd/mm/yyy but all i want is the month and the year.
So i tried
select datepart(mm, dtinsertdate)
from incident
which only gives me month and also used
select datepart(year,dtinsertdate) [year], datepart(month, dtinsertdate)[month],
Which works but i want both in the same column.
But when trying to get both mm, yyyy it looks like it only supports one datepart. Is there any way aroung this?
Many Thanks
John
Hi allPlease read my previous post here if this interests you:http://groups.google.co.uk/group/co...pu+usage&hl=en&I have (or rather a colleague has) found the situation that appears tobe causing this problem but I am still no further to finding asolution.It appears that if we execute a query against one particular table inthe database and that query requests data that is not indexed, or acolumn in the where clause is not indexed then this is when problemsoccur.The table cannot be copied using DTS reporting the error:"Error occurred copying row 3 - unspecified error".However, we can query using:"SELECT TOP n FROM <Table>"where n so far has been between 3 and 100000.However we cannot do SELECT * FROM <Table>.All we can guess at for now is that the problem occurs when a query isperformed to try and fetch data that is not indexed, using an index inthe where clause.i.e. SELECT <Non-indexed column> FROM <Table> WHERE <Indexed Column> =nCould it be possible that we have one 'rogue' row in the table somehowthat is causing the problem?The only thing to dispel this is that we can do the following:SELECT * FROM <Table> WHERE ID < 1which returns 1 row with ID = -1.We cannot do:SELECT * FROM <Table> WHERE ID < 200 even though we know that the onlyrecord matching this criteria is the record with ID = -1.Therefore the exact same data should be returned yet one query failsand one works.The actual problem that we see is that memory usage climbsdramatically, then once all memory is used, the CPU usage climbs to100% and stays there until we have to restart SQL Server.Any suggestions on this would be hugely appreciated.Thanks,Paul
View 4 Replies View RelatedI think I'm having problems because I don't really understand "Windows Authentication".
I've looked on the web and haven't seen any real explinations.
As an example, If I have 3 computers
1.ServerBox (xp pro) with SQL express (using Workgroups not Domains)
2 Laptop1
3 Laptop2
ServerBox and Laptop1 connected with cat5 or Wirless..Works fine.
Laptop2 connected with wirless. I see laptop2 in the serverbox "Network Places-Workgroup" on the serverbox
Laptop 1 has xp pro and uses sql authentication for "instance, db, usernane and password". Logging in is no problem (I use SQL authentication in connection string)
Laptop 2 has xp home edition and dosn't have to log in. I have the machine name, default user with no password and try Windows authentication. Does not work. error show:
Cannot open database "db1" requested by the login. I use Windows authentication connection string.
I've tested both connection strings in Laptop1 and they both work.
Does Windows Authentication read the clients login to his Windows Home Edition, and if so, does in need a password (I know its a good idea)..
So Laptop2 login to his Windows Home edition is Username=Jim and no password (shows administrator rights in his box.
Hello!
I have a strange problem in one SP. One small part of it wan't execute.
I have pasted whole procedure here and a solution to problem.
So, black on red part of code will not execute if black on blue code is in place.
But, if I supstitute black on blue with black on green, black on red part will execute!
Only thing I want to know is why is that hapening...
Regards
--SNIP--
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_FINKO_Knjizenje_Robnih]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_FINKO_Knjizenje_Robnih]
GO
CREATE procedure sp_FINKO_Knjizenje_Robnih
(
@VrDok varchar(8) = '', --Vrsta robnog dokumenta
@IDs varchar(2200) = '' --IDovi iz HeRPuSPrijenos_Knjizenja
)
AS
BEGIN
set @VrDok = rtrim(ltrim(@VrDok))
set @Ids = rtrim(ltrim(@Ids))
if @VrDok='' OR len(@IDs) = 0
BEGIN
RAISERROR ('Potrebna vrsta dokumenta i IDovi', 16, 1)
return
END
declare @vbCrLf char(2)
declare @SQL_temp nvarchar(4000)
set @vbcrlf = CHAR(13) + CHAR(10)
CREATE TABLE #tmpDokumenti (
[ID] [numeric] (18,0), [Dok_broj] [varchar] (32) COLLATE Croatian_CI_AS NULL, [AK_Skladiste] [varchar] (10) COLLATE Croatian_CI_AS NULL, [Dok_Vrsta] [varchar] (8)COLLATE Croatian_CI_AS NULL
)
set nocount ON
--Racunamo da imamo vrstu dokumenta te ajdiove te cemo sada brojeve dokumenata i skladista prebaciti u temp tablicu
set @SQL_temp= 'SELECT ID,Dok_Broj,AK_Skladiste,Dok_AK FROM HeRPuSPrijenos_Knjizenja' + @vbcrlf
set @SQL_temp= @SQL_Temp + 'WHERE (isnull(Temeljnica_Nastala,0)=0 AND Dok_OA = ''50'' AND Dok_AK=''' + @VrDok + ''')' + @vbcrlf
set @SQL_temp= @SQL_Temp + 'AND ID IN (' + @IDs + ') '
set @SQL_temp= 'SET NOCOUNT ON ' + @vbcrlf + @SQL_temp
INSERT INTO #tmpDokumenti
exec (@SQL_temp) --prebacujemo
SELECT v.oznaka_analitike
,v.Analiticki_konto
,v.Konto
,v.vrsta_dokumenta
,v.broj_dokumenta
,v.Datum_dokumenta
,sum(v.Duguje) as Duguje
,sum(v.Potražuje) as Potražuje
,v.Opis
,v.Vrsta_Temeljnice, v.Broj_Temeljnice, v.Datum_temeljnice
,v.Tecaj, v.Duguje2, v.Potražuje2
,v.Protukonto, v.Protuanalitika, v.ProtuAnliticki_konto
,v.Zatvoreno, v.Mjesec, v.Godina, v.Broj_dnevnika, v.Saldo
,v.Zatvoren_dnevnik, v.Valuta, v.IdNo, IDENTITY(int, 1,1) as rbr
,v.PodOznaka_analitike, v.Podanaliticki_konto
,v.Broj_izvornog_dokumenta, v.Vrsta_dokumentaIzvorni
,v.Broj_dokumentaIzvorni, v.Datum_dokumentaIzvorni
,v.DugujeDEV, v.PotrazujeDEV, v.NovcanaJedinica
,v.ZatvaraDokument
,v.Prijenos_ID, v.Prijenos_DokID
,v.Duguje2DEV, v.Potrazuje2DEV, v.ZatvorenoDEV
,v.Racunalo, v.Korisnik, v.DatumAzuriranja
,v.Prijenos_Dok_Dogadjaj, v.Prijenos_dok_zatvara
,v.Red_ID, v.Tem_ID,v.RVrDok as r_Vrsta_dokumenta
INTO #tmpTemeljnica
FROM vFK_RK as V INNER JOIN #tmpDokumenti as D
ON v.broj_dokumenta=d.dok_Broj AND V.AK_skladiste=D.AK_skladiste AND V.vrsta_dokumenta=@VrDok
GROUP BY v.Oznaka_analitike
,v.Analiticki_konto
,v.Konto
,v.vrsta_dokumenta
,v.broj_dokumenta
,v.Datum_dokumenta
,v.Opis
,v.Vrsta_Temeljnice, v.Broj_Temeljnice, v.Datum_temeljnice
,v.Tecaj, v.Duguje2, v.Potražuje2
,v.Protukonto, v.Protuanalitika, v.ProtuAnliticki_konto
,v.Zatvoreno, v.Mjesec, v.Godina, v.Broj_dnevnika, v.Saldo
,v.Zatvoren_dnevnik, v.Valuta, v.IdNo
,v.PodOznaka_analitike, v.Podanaliticki_konto
,v.Broj_izvornog_dokumenta, v.Vrsta_dokumentaIzvorni
,v.Broj_dokumentaIzvorni, v.Datum_dokumentaIzvorni
,v.DugujeDEV, v.PotrazujeDEV, v.NovcanaJedinica
,v.ZatvaraDokument
,v.Prijenos_ID, v.Prijenos_DokID
,v.Duguje2DEV, v.Potrazuje2DEV, v.ZatvorenoDEV
,v.Racunalo, v.Korisnik, v.DatumAzuriranja
,v.Prijenos_Dok_Dogadjaj, v.Prijenos_dok_zatvara
,v.Red_ID, v.Tem_ID,v.RVrDok--, v.RK_ID
,case --grupiranje po predznaku...
WHEN v.duguje < 0 THEN 'M'
WHEN v.potražuje < 0 THEN 'M'
ELSE 'P'
end
IF @@ERROR <>0
BEGIN
PRINT 'Greska: sp_FINKO_Knjienje_Robnih'
END
declare @Greska as bit
declare @ID_Knjiz as bigint
declare @VrDok1 as varchar(8)
declare @BrDok1 as varchar(8)
declare @Skl as varchar(8)
declare @Anal1 as varchar(8)
declare @Anal2 as varchar(8)
declare @Konto as varchar(8)
declare @Duguje as numeric(18,2)
declare @Potrazuje as numeric(18,2)
declare @SQL_Update as varchar(4000)
SET @Greska = 0 --ovdje pratimo jeli bilo kakvih gresaka a temeljnici
DECLARE rsTmp CURSOR LOCAL FAST_FORWARD READ_ONLY
FOR
SELECT d.ID,d.ak_skladiste,t.r_Vrsta_dokumenta,t.Broj_dokumenta,t.analiticki_konto,t.podanaliticki_konto,t.Konto,t.Duguje,t.Potražuje
FROM #tmpTemeljnica as t INNER JOIN #tmpDokumenti as d
ON t.r_vrsta_dokumenta=d.Dok_Vrsta and t.broj_dokumenta=d.Dok_broj
WHERE
(len(t.oznaka_analitike) = 2 and t.analiticki_konto is null)
OR
(len(t.Podoznaka_analitike) = 2 and t.Podanaliticki_konto is null)
OR
(t.Konto is null)
OR
(t.duguje=0 and t.potražuje=0)
ORDER by d.ID
OPEN rsTmp FETCH NEXT FROM rsTMP into @ID_Knjiz ,@Skl,@VrDok1,@BrDok1,@anal1,@Anal2,@Konto,@Duguje,@Potrazuje
WHILE (@@fetch_status=0)
BEGIN
IF @Anal1 IS NULL
BEGIN
BEGIN TRANSACTION T1
INSERT INTO HeRPuSPrijenos_Greske
(Knjizenje_ID,Modul,Stavka_ID,Stavka_Rbr,Broj_Greske,Opis_Greske1,Opis_Greske2,Opis_Greske3,VrijemeUpisa,Korisnik,Racunalo)
VALUES
(@ID_knjiz,'Robni prijenos',0,0,201,'Ne mogu pronaci analitiku za konto','','',getdate(),'server','server')
COMMIT TRANSACTION T1
SET @Greska=1
END
ELSE IF @Anal2 IS NULL
BEGIN
BEGIN TRANSACTION T1
INSERT INTO HeRPuSPrijenos_Greske
(Knjizenje_ID,Modul,Stavka_ID,Stavka_Rbr,Broj_Greske,Opis_Greske1,Opis_Greske2,Opis_Greske3,VrijemeUpisa,Korisnik,Racunalo)
VALUES
(@ID_knjiz,'Robni prijenos',0,0,202,'Ne mogu pronaci podanalitiku za konto','','',getdate(),'server','server')
COMMIT TRANSACTION T1
SET @Greska=1
END
ELSE IF @Konto IS NULL
BEGIN
BEGIN TRANSACTION T1
INSERT INTO HeRPuSPrijenos_Greske
(Knjizenje_ID,Modul,Stavka_ID,Stavka_Rbr,Broj_Greske,Opis_Greske1,Opis_Greske2,Opis_Greske3,VrijemeUpisa,Korisnik,Racunalo)
VALUES
(@ID_knjiz,'Robni prijenos',0,0,203,'Ne mogu pronaci konto','','',getdate(),'server','server')
COMMIT TRANSACTION T1
SET @Greska=1
END
ELSE IF @Duguje=0 AND @Potrazuje=0
BEGIN
BEGIN TRANSACTION T1
INSERT INTO HeRPuSPrijenos_Greske
(Knjizenje_ID,Modul,Stavka_ID,Stavka_Rbr,Broj_Greske,Opis_Greske1,Opis_Greske2,Opis_Greske3,VrijemeUpisa,Korisnik,Racunalo)
VALUES
(@ID_knjiz,'Robni prijenos',0,0,204,'Stavka za duguje i potrazuje ima 0','','',getdate(),'server','server')
COMMIT TRANSACTION T1
SET @Greska=1
END
FETCH NEXT FROM rsTMP into @ID_Knjiz ,@Skl,@VrDok1,@BrDok1,@anal1,@Anal2
END
CLOSE rsTmp
deallocate rsTmp
if @Greska= 0
BEGIN
declare @PojedinacniPrijenos bit
declare @Tem_vrsta varchar(8) --vrsta tekuce temeljnice
declare @Tem_broj bigint --sljedeci broj iz fin kartica
declare @Tem_Datum smalldatetime --danasnji datum
declare @Tem_ID uniqueidentifier --eto
declare @Knjiz_Mjesec numeric(18,0)
declare @Knjiz_Godina numeric(18,0)
declare @Knjiz_Dnevnik numeric(18,0)
SET @PojedinacniPrijenos=(
select isnull(osiguranje,0) FROM analiticka_konta where oznaka_analitike='50' and analiticki_konto=@VrDok
)
SET @Tem_vrsta =(
select top 1 vrsta_temeljnice from #tmpTemeljnica
)
SET @Tem_broj =(
SELECT isnull(max(dbo.ToNumeric(FK.Broj_temeljnice)),0) +1 --bilo sranje jer je broj_temeljnice varchar pa je sad ToNumeric
FROM Financijske_kartice as FK WHERE FK.Vrsta_temeljnice=@Tem_Vrsta
)
SET @Tem_Datum=CONVERT(CHAR(8),getdate(),112) --treba nam samo datum
SET @Tem_ID=newid()
SET @Knjiz_Mjesec=(
select isnull(podatak,0) from fink where segment='KNJIZIFIN' and naziv= 'Mjesec knjiženja'
)
SET @Knjiz_Godina=(
select isnull(podatak,0) from fink where segment='KNJIZIFIN' and naziv= 'Godina knjiženja'
)
SET @Knjiz_Dnevnik=(
select isnull(podatak,0) from fink where segment='KNJIZIFIN' and naziv= 'Broj dnevnika'
)
UPDATE #tmpTemeljnica SET
Broj_temeljnice =@Tem_broj
,Tem_ID =@Tem_ID
,Datum_temeljnice =@Tem_datum
,Mjesec =@Knjiz_Mjesec
,Godina =@Knjiz_Godina
,Broj_dnevnika =@Knjiz_Dnevnik
--idemo si napravit kopiju temp tablice(treba nam zbog grupnoh odnosno pojedinacnog prijenosa)
SELECT * INTO #tmpTemeljnica1
FROM #tmpTemeljnica WHERE 1=2
--idemo sad grupirat sve sto treba
SET @PojedinacniPrijenos =1 --zasad ostajemo na ovom
IF @PojedinacniPrijenos = 1
BEGIN
INSERT INTO #tmpTemeljnica1
SELECT v.oznaka_analitike
,v.Analiticki_konto
,v.Konto
,v.vrsta_dokumenta
,v.broj_dokumenta
,v.Datum_dokumenta
,sum(v.Duguje) as Duguje
,sum(v.Potražuje) as Potražuje
,v.Opis
,v.Vrsta_Temeljnice, v.Broj_Temeljnice, v.Datum_temeljnice
,v.Tecaj, v.Duguje2, v.Potražuje2
,v.Protukonto, v.Protuanalitika, v.ProtuAnliticki_konto
,v.Zatvoreno, v.Mjesec, v.Godina, v.Broj_dnevnika, v.Saldo
,v.Zatvoren_dnevnik, v.Valuta, v.IdNo
,v.PodOznaka_analitike, v.Podanaliticki_konto
,v.Broj_izvornog_dokumenta, v.Vrsta_dokumentaIzvorni
,v.Broj_dokumentaIzvorni, v.Datum_dokumentaIzvorni
,v.DugujeDEV, v.PotrazujeDEV, v.NovcanaJedinica
,v.ZatvaraDokument
,v.Prijenos_ID, v.Prijenos_DokID
,v.Duguje2DEV, v.Potrazuje2DEV, v.ZatvorenoDEV
,v.Racunalo, v.Korisnik, v.DatumAzuriranja
,v.Prijenos_Dok_Dogadjaj, v.Prijenos_dok_zatvara
,v.Red_ID, v.Tem_ID,''
FROM #tmpTemeljnica as v
GROUP by
v.oznaka_analitike
,v.Analiticki_konto
,v.Konto
,v.vrsta_dokumenta
,v.broj_dokumenta
,v.Datum_dokumenta
,v.Opis
,v.Vrsta_Temeljnice, v.Broj_Temeljnice, v.Datum_temeljnice
,v.Tecaj, v.Duguje2, v.Potražuje2
,v.Protukonto, v.Protuanalitika, v.ProtuAnliticki_konto
,v.Zatvoreno, v.Mjesec, v.Godina, v.Broj_dnevnika, v.Saldo
,v.Zatvoren_dnevnik, v.Valuta, v.IdNo
,v.PodOznaka_analitike, v.Podanaliticki_konto
,v.Broj_izvornog_dokumenta, v.Vrsta_dokumentaIzvorni
,v.Broj_dokumentaIzvorni, v.Datum_dokumentaIzvorni
,v.DugujeDEV, v.PotrazujeDEV, v.NovcanaJedinica
,v.ZatvaraDokument
,v.Prijenos_ID, v.Prijenos_DokID
,v.Duguje2DEV, v.Potrazuje2DEV, v.ZatvorenoDEV
,v.Racunalo, v.Korisnik, v.DatumAzuriranja
,v.Prijenos_Dok_Dogadjaj, v.Prijenos_dok_zatvara
,v.Red_ID, v.Tem_ID,
CASE --grupiranje po predznaku...
WHEN v.duguje < 0 THEN 'M'
WHEN v.potražuje < 0 THEN 'M'
ELSE 'P'
END
END
ELSE
BEGIN
print ''
END
INSERT INTO HeRPuSPrijenos_Temeljnica
SELECT
v.oznaka_analitike,v.Analiticki_konto
,v.Konto
,v.vrsta_dokumenta,v.broj_dokumenta,v.Datum_dokumenta
,v.Duguje,v.Potražuje
,v.Opis
,v.Vrsta_Temeljnice, v.Broj_Temeljnice, v.Datum_temeljnice
,v.Tecaj,v.Duguje2,v.Potražuje2
,v.Protukonto, v.Protuanalitika, v.ProtuAnliticki_konto
,v.Zatvoreno, v.Mjesec, v.Godina, v.Broj_dnevnika, v.Saldo
,v.Zatvoren_dnevnik, v.Valuta,v.rbr
,v.PodOznaka_analitike, v.Podanaliticki_konto
,v.Broj_izvornog_dokumenta, v.Vrsta_dokumentaIzvorni
,v.Broj_dokumentaIzvorni, v.Datum_dokumentaIzvorni
,v.DugujeDEV, v.PotrazujeDEV, v.NovcanaJedinica
,v.ZatvaraDokument
,v.Prijenos_ID, v.Prijenos_DokID
,v.Duguje2DEV, v.Potrazuje2DEV, v.ZatvorenoDEV
,v.Racunalo, v.Korisnik, v.DatumAzuriranja
,v.Prijenos_Dok_Dogadjaj, v.Prijenos_dok_zatvara
,v.Red_ID, v.Tem_ID--,v.RVrDok --, v.RK_ID
FROM #tmpTemeljnica1 as V
IF @@ERROR <> 0
BEGIN
BEGIN TRANSACTION T1
INSERT INTO HeRPuSPrijenos_Greske
(Knjizenje_ID,Modul,Stavka_ID,Stavka_Rbr,Broj_Greske,Opis_Greske1,Opis_Greske2,Opis_Greske3,VrijemeUpisa,Korisnik,Racunalo)
VALUES
(0,'Robni prijenos',0,0,205,'Greska prilikom prebacivanja podataka u HeRPuSPrijenos_Temeljnica','','',getdate(),'server','server')
COMMIT TRANSACTION T1
SET @Greska=1
END
END
-- DROP TABLE #tmpDokumenti --necemo dropat jel ce server dropat nakon zavrsetka procedure
-- DROP TABLE #tmpTemeljnica
-- DROP TABLE #tmpTemeljnica1
IF @Greska=0
BEGIN
set @SQL_Update='update HeRPuSPrijenos_Knjizenja set Temeljnica_Nastala=1, Temeljnica_Greska=0 Where ID IN (' + @vbcrlf
set @SQL_Update=@SQL_Update + @IDs + ') AND Dok_AK=''' + @VrDok + '''' + @vbcrlf
EXEC (@SQL_Update)
UPDATE robne_kartice SET Knjizenje_financijsko=1 WHERE ID IN --updejta robne kartice na knjizeno
(
SELECT v.RK_ID
FROM vfk_rk as v INNER JOIN #tmpDokumenti as D
ON v.broj_dokumenta=d.dok_Broj AND V.AK_skladiste=D.AK_skladiste AND V.vrsta_dokumenta=@VrDok
)
END
ELSE
BEGIN
set @SQL_Update='update HeRPuSPrijenos_Knjizenja set Temeljnica_Nastala=0, Temeljnica_Greska=1 Where ID IN (' + @vbcrlf
set @SQL_Update=@SQL_Update + @IDs + ') AND Dok_AK=''' + @VrDok + '''' + @vbcrlf
EXEC (@SQL_Update)
END
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
--SNIP--
update HeRPuSPrijenos_Knjizenja set Temeljnica_Nastala=1, Temeljnica_Greska=0 Where
ID IN (select ID from #tmpDokumenti)
I created a Tabular report... The data string is
Code Snippet
SELECT dbo.forehist_txt.Item, dbo.forehist_txt.Class1, dbo.forehist_txt.Description, dbo.forehist_txt.Price, dbo.DSItem2_txt.UserDefined1,
dbo.DSItem2_txt.UserDefined3, dbo.forehist_txt.AdjustedHistory15, dbo.forehist_txt.AdjustedHistory14, dbo.forehist_txt.AdjustedHistory13,
dbo.forehist_txt.AdjustedHistory12, dbo.forehist_txt.AdjustedHistory11, dbo.forehist_txt.AdjustedHistory10, dbo.forehist_txt.AdjustedHistory9,
dbo.forehist_txt.AdjustedHistory8, dbo.forehist_txt.AdjustedHistory7, dbo.forehist_txt.AdjustedHistory6, dbo.forehist_txt.AdjustedHistory5,
dbo.forehist_txt.AdjustedHistory4, dbo.forehist_txt.AdjustedHistory3, dbo.forehist_txt.AdjustedHistory2, dbo.forehist_txt.AdjustedHistory1,
dbo.forehist_txt.SystemForecast1, dbo.forehist_txt.SystemForecast2, dbo.forehist_txt.SystemForecast3, dbo.forehist_txt.SystemForecast4,
dbo.forehist_txt.SystemForecast5, dbo.forehist_txt.SystemForecast6, dbo.forehist_txt.SystemForecast7
FROM dbo.forehist_txt INNER JOIN
dbo.DSItem2_txt ON dbo.forehist_txt.Item = dbo.DSItem2_txt.Item AND dbo.forehist_txt.Company = dbo.DSItem2_txt.Company AND
dbo.forehist_txt.Division = dbo.DSItem2_txt.Division AND dbo.forehist_txt.Corporation = dbo.DSItem2_txt.Corporation AND
dbo.forehist_txt.Sold = dbo.DSItem2_txt.Sold AND dbo.forehist_txt.Department = dbo.DSItem2_txt.Department AND
dbo.forehist_txt.ShipTo = dbo.DSItem2_txt.ShipTo
I have created two Parameters... ProductManagers (i.e. John) and Comments (i.e. Top 500).
ProductManagers is Data type: String. Multi-value is checked. I created a new dataset (ProductManagers) so that way the values are only one instance instead of several instances. Available values: From query. Default values: Null.
Comments is Data type: Sting. Multi-value is checked. I created a new dataset so that way the values are only one instance instead of several instances. Available values: From query. Default values: Null.
PROBLEM:
When I click Preview to preview the report and select the desired parameters, my report is not filtered by the parameters. It simply returns every single record.
So two Questions:
1) How do I get the report to return results based off of the parameters?
2) How do I setup the parameters so that I can filter results by John only, Top 500 only, and John OR Top 500?
Hi,
I think this is a simple question but I am just not sure how to do it right, as a newbie.
All I want to do is generate a report of company employees grouped by their date of hire.
SELECT DateOfHire, FirstName, LastName from Empls
ORDER BY DateOfHire
The only problem is that the DateOfHire displays as, e.g., '01/03/2004 12:00:00' which is not what I want. I SIMPLY WANT TO OMIT THE TIME PART but don't know quite how to say this. I tried several variations along this line but it causes scoping errors...
SELECT CONVERT(Varchar(10), DateOfHire, 101) AS DOHDateOnly, FirstName, LastName, State
FROM EMPL
GROUP BY DOHDateOnly
but this gives error
"The value expressions for the textbox ‘DateOfHire’ refers to the field ‘DOHDateOnly’. Report item expressions can only refer to fields within the current data set scope or, if inside an aggregate, the specified data set scope."
Which makes sense, but I don't know HOW to say the right thing to get what I want.
Any help will be greatly appreciated.
Something strange just happened to my database.Windows Vista UltimateSQL Server 2005 SP2IIS 7.0I just did an insert into a few tables from my ASP.NET application that I'm building and I suddenly cannot query the database tables. The query window just spins and finally times out.So I decided to try a SELECT TOP and tried 100, nothing, then 10, nothing, then 1 and it returned the first row. Then I tried 2 and got that, and I remember this table had 8 rows prior to my last Insert, and once I tried to select TOP 9, it times out again.Does anyone knows why this happened and how to fix it? It's like every row past 8 either doesn't exist or got corrupted somehow. I can't even drop the table and rebuild it. SQL Server won't let me. If someone doesn't have a solution, I'll have to rebuild the entire database and I'd rather not have to do that.Any help would be appreciated.Thanks,Kahanu
View 2 Replies View Relatedi want to use the date part of the function getdate in my sql query.
the getdate function return the current date and time. i want only the date part.
how can i do it.
Is there a chance to retreive let's say rows positioned from 30-70 if a query produced result that contains 100 rows?
View 3 Replies View RelatedHi,I'm using MSSQL2k and I have some customers in a customer table. I want to write a webapp and offer them the possibility to teach them using the app while having telephone contact with them. They can use the app on my server for some time and add data to it.After a while when someone wants to buy the app I would like to distribute the app as an Intranet app to their own server and let them have the data they already have wrote into the db on my server.Because there will be some customers with much data I'd like some guidance in how to plan the database to easily extract their own data and create a new db polutated with their data.Kenneth
View 1 Replies View RelatedI have a column Sports which contains a string.the string is comma delimited, so may contain e.g. 1,3,2,6,8,19 or 6,22,13 etc.What is (performance wise) the fastest way to select all the rows where the number 2 is in the Sports column.....?(I can't search on ",2," since "2" may also be at the start of the string....)
View 1 Replies View RelatedI have another question regarding migration process to diffrent SQL Server.
Last time when I did migration I didn't restore system databases (master in particular).
I only restored user databases and of course I've got login errors such
Microsoft SQL-DMO (ODBC SQLState: 42000) Error 15023: User or role '%s' already exists in the current database.
I just used sp_change_users_login to fix those orphans.
I've read articles in MS knowledge base and not quite sure is this a good idea to restore master database prior to restoring all user databases on the new server in order to keep all logins?
I'll try to use recommended article for transfering logins with password instead of restoring master database from backup
http://support.microsoft.com/support/kb/articles/Q246/1/33.ASP
Any advice is greatly appreciated.
Lena
I am sorry to continue bothering this forum with the
continuation of this question but here it is. And thank you to Craig for
giving me the equivalent of the function Date() in SQL. Now when I pull from
the SQl Server with the old ASP pull with this statement using GETDATE()
strSQLQuery1 = "SELECT * FROM cocoitem WHERE CustNum = '" & strcustnum & "' AND (stat = 'O' OR stat = 'F') AND [due-date] > DateAdd('yyyy', -1, GETDATE()) Order By [cust-item], [due-date] ASC;"
I get this:
Microsoft OLE DB Provider for SQL Server error '80040e14'
Invalid parameter 1 specified for dateadd.
/scripts/order/shippingstatsopen.asp, line 28
So I guess I need to also know the equivalent of DateAdd . Also, does anyone
know of a Access Function to Sql 7 function comparison chart so I can write
for the new database comprehendingly?
Thank you very much for your help in advance.
I have used the Microsoft code below to create a link to SQL server from access. I am using DAO not ADODB. Someone said that I should use dblib instead of ODBC in the connection string below in order to get the connection to work.
Does anyone know the correct syntax for OLE DB connection string and can it be used with DAO? I am getting an error "ISAM path not found"?? Seems I have wrong connection string syntax?? Please advise.
Thanks again for your help.
-------------------------------------------------------------------------
Sub ClientServerX3()
Dim dbsCurrent as Database
Dim tdRoy as TableDef
Set dbsCurrent = CurrentDb
Set tdfRoy = dbsCurrent.CreateTableDef("Roy")
tdfRoy.Connect = "ODBC;DATABASE=pubs;UID=sa;PWD=;DSN=Publishers"
***************IS THERE ANYWAY TO USE AN OLEDB (OR OTHER DSN-LESS)
***************CONNECTION HERE? How??
tdfRoy.SourceTableName = "Royalties"
dbsCurrent.TableDefs.Append tdfRoy
End Sub
I am trying to strip off 'XYZ' from column1 in table1 whenever it occurs
Any help appreciated
saad
Trying to update part of a field. Currently using ColdFusion 4.0 and SQL Server 7.0.
My field looks something like this: ABC.DEF.GHI and I just want to update the last 3 characters, GHI. The length of the field may change so it's not going to be 11 characters long.
Any help would be appreciated.
Tell me please how can I make a foreing key constraint for a table using from another not all primary key or not all data. For example, I need to link a table "subject" with primary key "dscp_num, depart" using depart as a foreing key. But there is no table with such primary key, there is only table "codif_values" where selecting records by condition "codif_num = 1" gives the relation with necesary primary key to link. Also I need to link "subject" table as a primary key table with the table "ses_curr". But this table doesn't include "depart" field. It includes field "reg_num" that is a primary key in table "students". This table include field "stgroup" as a foreing key for table "groups". The last table includes "speciality" field that is a foreing key for table "specialities". And only this table includes "depart" field. Tell me please how I can make a foreing key constraint for table "ses_curr" by table "subject".
View 3 Replies View Related------------
D.P. at 3/15/99 2:04:04 PM
Using VB5|6/ADO/MSSQL6.5. I want to get the names of tables in a query. I.e. if query is "Select Name, Title from Employee inner join job on employee.empid = job.empid" I want a way to get "Name, Title"
In VB 3 we used the VBSQL.VBX which had 2 functions for this: SQLTabCount and SQLTabName.
Thanks in advance
DP
------------
Gregory at 3/15/99 2:25:31 PM
dp ++++++++++++++++++
see my responses below
dp ++++++++++++++++++
if u need to get "Name" and "Title" - u're trying to get column(field) names??
-or-
if u need to get table names involved in a query - u need to get "Employee" and "Job"
so what exactly do u need??
********
dp ++++++++++++++++++
My bad... I wanted Employee, Job. The Table names.
dp ++++++++++++++++++
if u need to see column names - u can use Fields collection to get all fields in a recordset
I don't know if there's a way to get table names of tables involved in a query, in ADO.in RDO there's a property of a rdoColumn object to get table name (which doesn't work always anyway).
********
dp ++++++++++++++++++
actually this can be done in ADO. You can get the table from which the column was derived by using rs.fields(##).properties("BASETABLENAME") but I don't want to rely on this because I may not have a field from each table... What I really want is creat a recorset and ask it what tables it's drawing from. Again, VBSQL had SQLTabCount which gave you the number of tables in the query and then SQLTabName would give you the names by table number
dp ++++++++++++++++++
or, in select statement u can aliases:
select Name as 'Employee_Name',
title as 'Job_Title'
from employee, job
where employee.job_id = job.job_id
so, when you get all fields from fields collection, u can check field name
dp ++++++++++++++++++
yeah... i'll do this if I have to... looking for a better way (hopefully) first
dp ++++++++++++++++++
********
or , u can use ODBC API functions to get that information (for example using SQLColumns() function) - but, u'll need to connect first, to get connection handle, then prepare or execute statement to get statement handle, and then get information about columns (and table names).
dp ++++++++++++++++++
this sounds interesting.... where might I find more info about this??? If i create an ado recorset, will I be able to "analyze" it in this way via ODBC?
dp ++++++++++++++++++
How can I make my search button have the "Any part of field" match as a default? with a simple query...
I have a field in MS access with hundreds of words (cv)... I want to be able to find a word in "Any part of field"
my try:
WHERE ((([cv].[detail cv])=[detail]));
detail is nowhere to find... i am prompt to give a value. fine.but it
equals whole field; detail must be the sole value of the field detail cv...
help!
mchel