I have 1 table:
ID pagename datevisited
1default01/01/2006
1info01/01/2006
1default02/01/2006
1info02/01/2006
1summary02/01/2006
2default02/01/2006
2info02/01/2006
2summary02/01/2006
I need to run query for ID who didn't visited a summary page(per day)
How do I do this?
I have a table which contains a sports schedule:
team1_id,team2_id,result1,result2
result1 is for team1_id
result2 is for team2_id
I want to run a query to get summarize the two id fields with their assoiciated results:
For Example the dataset might look like this:
row 1 - id1=20,id2=30,result1=1,result2=3
row 2 - id1=30,id2=20,result1=2,result2=2
row 3 - id1=20,id2=40,result1=3,result2=1
row 4 - id1=40,id2=20,result1=1,result2=3
how do I build the query to merge the ids and thier respective results into one countable dataset
hi ..
my problem is
i create a user in sqlserver from enterprise manager as follows:-
1- goto logins and new login
2-type a name and choose sqlserver authentication and type a password
3- from server roles tab i choose system administrators
4 from database access i choose the database i need that user to connect to .and OK
5-i goto database node in enterprise manager and select the database and the table
and double click on it and choose permissions and de-select the SELECT privilige
then i use ADO to connect to sqlserver through that user and when i select(SQL query) the table the data appeared normally !!!! and it suppose to give me exception
thanks in advance.
I am trying to sum up sales for employees and get the employee with the highest sales in one query. The query I have below works but it doesnt get me the EmployeeID. Assume all the fields are in the same table. If I try to do something like this it bombs on me: "MAX(SUM(OrderAmount)"
SELECT Max(OrdersSum) AS MaxOrders FROM (SELECT SUM(OrderAmount) as OrdersSum
FROM Orders
GROUP BY EmployeeID);
Thanks in advance!
I'm trying to make a summary daily production report on the data below:Want to summarize the data with the sum of the Correct Weight between start and end date.
eg. of summary.
Recipe Name Total Weight
Hedge Shears - Lasher/kuduÂ
500
Grass SlasherÂ
200
eg.
But it needs to summarize when selected between start and end date.
RecipeName
CorrectWeight
CurDateTime
Weight
[code]....
...but apparently not me, I'm very new with this T-SQL stuff and am seeking the advice of the seasoned pros at this forum.
Description on my SQL-5 Environment:
Table I Sales: Prod_ID, Prod_DT, Sales_DT, Buyer_Name, Buyer_State
Table II Repairs: Prod_ID, Prod_DT, Sales_DT, Repair_DT
These 2 tables are joined by the common key Prod_ID & also and share the product's production & sales dates. What I would like to do is produce a rate summary similar to description below.
Production_YYYY, Production_MM, Sales_Cnt, Repairs_Cnt, Repair_Rate((Repairs_CNT/Sales_CNT)*100)
Important to remember that not all products experience repairs, so the basis for Sales_CNT needs to be the Sales Table, even thou Prod_DT also appears in Repairs Table.
It's simple enough for novice like me to produce 2 tables independently and then merge back those resulting tables into the single table output described above. But my question is how do I write a single SQL "SELECT" request that will produce the results into just a single table.
Thanks in advance for your help!
I have two tables T1 and T2
T1 is having User : Visiting Date
T2 IS having User : Last Visited Date
Now I have to update T2 with last visited date based on T1 Entry.
Can I roll back certain query(insert/update) execution in one page if query (insert/update) in other page execution fails in asp.net.( I am using sqlserver 2000 as back end)
scenario
In a webpage1, I have insert query into master table and Page2 I have insert query to store data in sub table.
I need to rollback the insert command execution for sub table ,if insert command to master table in web page1 is failed. (Query in webpage2 executes first, then only the query in webpage1) Can I use System. Transaction to solve this? Thanks in advance
Hey, i've written a query to search a database dependant on variables chosen by user etc etc. Opened up a new sqldatasource, entered the query shown below and went on to the test query page. Entered some test variables, everything works as it should do. Try to get it to show in a datagrid on a webpage - nothing. No data shows.
SELECT dbo.DERIVATIVES.DERIVATIVE_ID, count(*) AS Matches
FROM dbo.MAKES INNER JOIN
dbo.MODELS ON dbo.MAKES.MAKE_ID = dbo.MODELS.MAKE_ID INNER JOIN
dbo.DERIVATIVES ON dbo.MODELS.MODEL_ID = dbo.DERIVATIVES.MODEL_ID INNER JOIN
dbo.[VALUES] ON dbo.DERIVATIVES.DERIVATIVE_ID = dbo.[VALUES].DERIVATIVE_ID INNER JOIN
dbo.ATTRIBUTES ON dbo.[VALUES].ATTRIBUTE_ID = dbo.ATTRIBUTES.ATTRIBUTE_ID
WHERE ((ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID1 and (@VAL1 is null or VALUE = @VAL1)) or
(ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID2 and (@VAL2 is null or VALUE = @VAL2)) or
(ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID3 and (@VAL3 is null or VALUE = @VAL3)) or
(ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID4 and (@VAL4 is null or VALUE = @VAL4)) )
GROUP BY dbo.DERIVATIVES.DERIVATIVE_ID
HAVING count(*) >= CASE WHEN @VAL1 IS NOT NULL THEN 1 ELSE 0 END +
CASE WHEN @VAL2 IS NOT NULL THEN 1 ELSE 0 END +
CASE WHEN @VAL3 IS NOT NULL THEN 1 ELSE 0 END +
CASE WHEN @VAL4 IS NOT NULL THEN 1 ELSE 0 END -2
ORDER BY count(*) DESC
Here is the page source
<%@ Page Language="VB" MasterPageFile="~/MasterPage.master" Title="Untitled Page" %>
<asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:DevConnectionString1 %>"
SelectCommand="	SELECT dbo.DERIVATIVES.DERIVATIVE_ID, count(*) AS Matches 	FROM dbo.MAKES INNER JOIN 				 dbo.MODELS ON dbo.MAKES.MAKE_ID = dbo.MODELS.MAKE_ID INNER JOIN 				 dbo.DERIVATIVES ON dbo.MODELS.MODEL_ID = dbo.DERIVATIVES.MODEL_ID INNER JOIN 				 dbo.[VALUES] ON dbo.DERIVATIVES.DERIVATIVE_ID = dbo.[VALUES].DERIVATIVE_ID INNER JOIN 				 dbo.ATTRIBUTES ON dbo.[VALUES].ATTRIBUTE_ID = dbo.ATTRIBUTES.ATTRIBUTE_ID 	WHERE ((ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID1 and (@VAL1 is null or VALUE = @VAL1)) or 		 (ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID2 and (@VAL2 is null or VALUE = @VAL2)) or 		 (ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID3 and (@VAL3 is null or VALUE = @VAL3)) or 		 (ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID4 and (@VAL4 is null or VALUE = @VAL4)) ) 	GROUP BY dbo.DERIVATIVES.DERIVATIVE_ID 	HAVING count(*) >= CASE WHEN @VAL1 IS NOT NULL THEN 1 ELSE 0 END + 									 CASE WHEN @VAL2 IS NOT NULL THEN 1 ELSE 0 END + 									 CASE WHEN @VAL3 IS NOT NULL THEN 1 ELSE 0 END + 									 CASE WHEN @VAL4 IS NOT NULL THEN 1 ELSE 0 END -2 	ORDER BY count(*) DESC ">
<SelectParameters>
<asp:ControlParameter ControlID="DropDownList1" Name="ATT_ID1" PropertyName="SelectedValue" />
<asp:ControlParameter ControlID="TextBox1" Name="VAL1" PropertyName="Text" />
<asp:Parameter Name="ATT_ID2" />
<asp:Parameter Name="VAL2" />
<asp:Parameter Name="ATT_ID3" />
<asp:Parameter Name="VAL3" />
<asp:Parameter Name="ATT_ID4" />
<asp:Parameter Name="VAL4" />
</SelectParameters>
</asp:SqlDataSource>
<asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:DevConnectionString1 %>"
SelectCommand="SELECT * FROM [ATTRIBUTES]"></asp:SqlDataSource>
<br />
<asp:DropDownList ID="DropDownList1" runat="server" DataSourceID="SqlDataSource2"
DataTextField="ATTRIBUTE_NAME" DataValueField="ATTRIBUTE_ID">
</asp:DropDownList>
<asp:TextBox ID="TextBox1" runat="server" AutoPostBack="True"></asp:TextBox><br />
<br />
<br />
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="DERIVATIVE_ID"
DataSourceID="SqlDataSource1">
<Columns>
<asp:BoundField DataField="DERIVATIVE_ID" HeaderText="DERIVATIVE_ID" InsertVisible="False"
ReadOnly="True" SortExpression="DERIVATIVE_ID" />
<asp:BoundField DataField="Matches" HeaderText="Matches" ReadOnly="True" SortExpression="Matches" />
</Columns>
</asp:GridView>
</asp:Content>
AFAIK I have configured the source to pick up the dropdownlist value and the textbox value (the text box is autopostback).
Am i not submitting the data correctly? (It worked with a simple query...just not with this one). I have tried a stored procedure which works when testing just not when its live on a webpage.
Please help!
(Visual Web Devleoper 2005 Express and SQL Server Management Studio Express)
I have created one reports but all the records are displaying on one page.find a solution to display the records page by page. I created the same report without group so the records are displaying in page by page.
View 3 Replies View RelatedSo any help? I tried the stuff in the FAQ
View 3 Replies View RelatedFirst latest Identity column is 5 I try to insert value to table which have Identity column by ExecuteNonQuery() method. There was some error and rollback this transaction.and then I modified code for completely saving after that I try to insert value again by running program although this transaction completely But value in identity is 7 ( It seems value 6 didn't rollback when not completely saveing ) Please advise me How can I rollback Identity column value ?
View 1 Replies View RelatedI have a transaction log that is 80% full and will not dump. I have used the "dump transaction dbname with no_log" command. It return the usual "This command did not return data, and it did not return any rows". However, this didn't do anything. The transaction log is still full. I don't want to restart the server if I don't have to. Any ideas?
View 5 Replies View RelatedWe have Active/Passive cluster on Windows 2003. It is a 2 nodes cluster. The active server came down and the Cluster didn't failover. Then we had to manually failover. Can you help me to decipher the Cluster.log to find out why it didn't failover?
00000d08.000012a8::2007/10/12-04:04:40.847 ERR SQL Server <SQL Server>: [sqsrvres] CheckQueryProcessorAlive: sqlexecdirect failed
00000d08.000012a8::2007/10/12-04:04:40.847 ERR SQL Server <SQL Server>: [sqsrvres] printODBCError: sqlstate = HYT00; native error = 0; message = [Microsoft][SQL Native Client]Query timeout expired
00000d08.000012a8::2007/10/12-04:04:40.847 ERR SQL Server <SQL Server>: [sqsrvres] OnlineThread: QP is not online.
0000077c.000003dc::2007/10/12-04:04:43.785 INFO [CP] CppRegNotifyThread checkpointing key SoftwareMicrosoftMicrosoft SQL ServerMSSQL.1MSSQLSERVER to id 4 due to timer
0000077c.000003dc::2007/10/12-04:04:43.785 INFO [Qfs] QfsGetTempFileName C:DOCUME~1SVC_CL~1LOCALS~1Temp, CLS, 13522 => C:DOCUME~1SVC_CL~1LOCALS~1TempCLS34D2.tmp, status 0
0000077c.000003dc::2007/10/12-04:04:43.785 INFO [Qfs] QfsDeleteFile C:DOCUME~1SVC_CL~1LOCALS~1TempCLS34D2.tmp, status 0
0000077c.000003dc::2007/10/12-04:04:43.785 INFO [Qfs] QfsRegSaveKey C:DOCUME~1SVC_CL~1LOCALS~1TempCLS34D2.tmp, status 0
0000077c.000003dc::2007/10/12-04:04:43.785 INFO [CP] CpSaveData: checkpointing data id 4 to quorum node 1
0000077c.000003dc::2007/10/12-04:04:43.785 INFO [CP] CppWriteCheckpoint checkpointing file C:DOCUME~1SVC_CL~1LOCALS~1TempCLS34D2.tmp to file Q:MSCS 592a048-5457-426a-a46d-996441ef0182 0000004.CPT
0000077c.000003dc::2007/10/12-04:04:43.785 INFO [Qfs] QfsCreateDirectory Q:MSCS 592a048-5457-426a-a46d-996441ef0182, status 183
0000077c.000003dc::2007/10/12-04:04:43.785 INFO [Qfs] QfsOpenFile C:DOCUME~1SVC_CL~1LOCALS~1TempCLS34D2.tmp => 3, 36cf960 status 0
0000077c.000003dc::2007/10/12-04:04:43.785 INFO [Qfs] QfsOpenFile Q:MSCS 592a048-5457-426a-a46d-996441ef0182 0000004.CPT => 2, 36cf8d0 status 183
0000077c.000003dc::2007/10/12-04:04:43.785 INFO [Qfs] ReadFile 928 (regf) 32768 16384, (0=>0) 0 status 0
0000077c.000003dc::2007/10/12-04:04:43.785 INFO [Qfs] WriteFile 95c (regf) 16384, status 0 (0=>0)
0000077c.000003dc::2007/10/12-04:04:43.785 INFO [Qfs] ReadFile 928 (regf) 32768 0, (0=>0) 0 status 0
0000077c.000003dc::2007/10/12-04:04:43.785 INFO [Qfs] QfsFlushBuffers 95c, status 0
0000077c.000003dc::2007/10/12-04:04:43.785 INFO [Qfs] QfsCloseHandle 928, status 0
0000077c.000003dc::2007/10/12-04:04:43.785 INFO [Qfs] QfsCloseHandle 95c, status 0
0000077c.000003dc::2007/10/12-04:04:43.785 INFO [Qfs] QfsDeleteFile C:DOCUME~1SVC_CL~1LOCALS~1TempCLS34D2.tmp, status 0
0000077c.000003dc::2007/10/12-04:06:23.084 INFO [CP] CppRegNotifyThread checkpointing key SoftwareMicrosoftMicrosoft SQL ServerMSSQL.1MSSQLSERVER to id 4 due to timer
0000077c.000003dc::2007/10/12-04:06:23.084 INFO [Qfs] QfsGetTempFileName C:DOCUME~1SVC_CL~1LOCALS~1Temp, CLS, 13523 => C:DOCUME~1SVC_CL~1LOCALS~1TempCLS34D3.tmp, status 0
0000077c.000003dc::2007/10/12-04:06:23.084 INFO [Qfs] QfsDeleteFile C:DOCUME~1SVC_CL~1LOCALS~1TempCLS34D3.tmp, status 0
0000077c.000003dc::2007/10/12-04:06:23.084 INFO [Qfs] QfsRegSaveKey C:DOCUME~1SVC_CL~1LOCALS~1TempCLS34D3.tmp, status 0
0000077c.000003dc::2007/10/12-04:06:23.084 INFO [CP] CpSaveData: checkpointing data id 4 to quorum node 1
0000077c.000003dc::2007/10/12-04:06:23.084 INFO [CP] CppWriteCheckpoint checkpointing file C:DOCUME~1SVC_CL~1LOCALS~1TempCLS34D3.tmp to file Q:MSCS 592a048-5457-426a-a46d-996441ef0182 0000004.CPT
0000077c.000003dc::2007/10/12-04:06:23.084 INFO [Qfs] QfsCreateDirectory Q:MSCS 592a048-5457-426a-a46d-996441ef0182, status 183
0000077c.000003dc::2007/10/12-04:06:23.084 INFO [Qfs] QfsOpenFile C:DOCUME~1SVC_CL~1LOCALS~1TempCLS34D3.tmp => 3, 36cf960 status 0
0000077c.000003dc::2007/10/12-04:06:23.084 INFO [Qfs] QfsOpenFile Q:MSCS 592a048-5457-426a-a46d-996441ef0182 0000004.CPT => 2, 36cf8d0 status 183
0000077c.000003dc::2007/10/12-04:06:23.084 INFO [Qfs] ReadFile 95c (regf) 32768 16384, (0=>0) 0 status 0
0000077c.000003dc::2007/10/12-04:06:23.084 INFO [Qfs] WriteFile 928 (regf) 16384, status 0 (0=>0)
0000077c.000003dc::2007/10/12-04:06:23.084 INFO [Qfs] ReadFile 95c (regf) 32768 0, (0=>0) 0 status 0
0000077c.000003dc::2007/10/12-04:06:23.084 INFO [Qfs] QfsFlushBuffers 928, status 0
0000077c.000003dc::2007/10/12-04:06:23.084 INFO [Qfs] QfsCloseHandle 95c, status 0
0000077c.000003dc::2007/10/12-04:06:23.084 INFO [Qfs] QfsCloseHandle 928, status 0
0000077c.000003dc::2007/10/12-04:06:23.084 INFO [Qfs] QfsDeleteFile C:DOCUME~1SVC_CL~1LOCALS~1TempCLS34D3.tmp, status 0
0000077c.000003dc::2007/10/12-04:08:08.259 INFO [CP] CppRegNotifyThread checkpointing key SoftwareMicrosoftMicrosoft SQL ServerMSSQL.1MSSQLSERVER to id 4 due to timer
0000077c.000003dc::2007/10/12-04:08:08.259 INFO [Qfs] QfsGetTempFileName C:DOCUME~1SVC_CL~1LOCALS~1Temp, CLS, 13524 => C:DOCUME~1SVC_CL~1LOCALS~1TempCLS34D4.tmp, status 0
0000077c.000003dc::2007/10/12-04:08:08.259 INFO [Qfs] QfsDeleteFile C:DOCUME~1SVC_CL~1LOCALS~1TempCLS34D4.tmp, status 0
0000077c.000003dc::2007/10/12-04:08:08.259 INFO [Qfs] QfsRegSaveKey C:DOCUME~1SVC_CL~1LOCALS~1TempCLS34D4.tmp, status 0
0000077c.000003dc::2007/10/12-04:08:08.259 INFO [CP] CpSaveData: checkpointing data id 4 to quorum node 1
0000077c.000003dc::2007/10/12-04:08:08.259 INFO [CP] CppWriteCheckpoint checkpointing file C:DOCUME~1SVC_CL~1LOCALS~1TempCLS34D4.tmp to file Q:MSCS 592a048-5457-426a-a46d-996441ef0182 0000004.CPT
0000077c.000003dc::2007/10/12-04:08:08.259 INFO [Qfs] QfsCreateDirectory Q:MSCS 592a048-5457-426a-a46d-996441ef0182, status 183
0000077c.000003dc::2007/10/12-04:08:08.259 INFO [Qfs] QfsOpenFile C:DOCUME~1SVC_CL~1LOCALS~1TempCLS34D4.tmp => 3, 36cf960 status 0
0000077c.000003dc::2007/10/12-04:08:08.259 INFO [Qfs] QfsOpenFile Q:MSCS 592a048-5457-426a-a46d-996441ef0182 0000004.CPT => 2, 36cf8d0 status 183
0000077c.000003dc::2007/10/12-04:08:08.259 INFO [Qfs] ReadFile 7e0 (regf) 32768 16384, (0=>0) 0 status 0
0000077c.000003dc::2007/10/12-04:08:08.259 INFO [Qfs] WriteFile 95c (regf) 16384, status 0 (0=>0)
0000077c.000003dc::2007/10/12-04:08:08.259 INFO [Qfs] ReadFile 7e0 (regf) 32768 0, (0=>0) 0 status 0
0000077c.000003dc::2007/10/12-04:08:08.259 INFO [Qfs] QfsFlushBuffers 95c, status 0
0000077c.000003dc::2007/10/12-04:08:08.259 INFO [Qfs] QfsCloseHandle 7e0, status 0
0000077c.000003dc::2007/10/12-04:08:08.259 INFO [Qfs] QfsCloseHandle 95c, status 0
0000077c.000003dc::2007/10/12-04:08:08.259 INFO [Qfs] QfsDeleteFile C:DOCUME~1SVC_CL~1LOCALS~1TempCLS34D4.tmp, status 0
0000077c.00000844::2007/10/12-04:09:11.323 INFO [Qfs] GetDiskFreeSpaceEx Q:MSCS, status 0
0000077c.000003dc::2007/10/12-04:09:56.652 INFO [CP] CppRegNotifyThread checkpointing key SoftwareMicrosoftMicrosoft SQL ServerMSSQL.1MSSQLSERVER to id 4 due to timer
0000077c.000003dc::2007/10/12-04:09:56.652 INFO [Qfs] QfsGetTempFileName C:DOCUME~1SVC_CL~1LOCALS~1Temp, CLS, 13525 => C:DOCUME~1SVC_CL~1LOCALS~1TempCLS34D5.tmp, status 0
0000077c.000003dc::2007/10/12-04:09:56.652 INFO [Qfs] QfsDeleteFile C:DOCUME~1SVC_CL~1LOCALS~1TempCLS34D5.tmp, status 0
0000077c.000003dc::2007/10/12-04:09:56.652 INFO [Qfs] QfsRegSaveKey C:DOCUME~1SVC_CL~1LOCALS~1TempCLS34D5.tmp, status 0
0000077c.000003dc::2007/10/12-04:09:56.652 INFO [CP] CpSaveData: checkpointing data id 4 to quorum node 1
0000077c.000003dc::2007/10/12-04:09:56.652 INFO [CP] CppWriteCheckpoint checkpointing file C:DOCUME~1SVC_CL~1LOCALS~1TempCLS34D5.tmp to file Q:MSCS 592a048-5457-426a-a46d-996441ef0182 0000004.CPT
0000077c.000003dc::2007/10/12-04:09:56.652 INFO [Qfs] QfsCreateDirectory Q:MSCS 592a048-5457-426a-a46d-996441ef0182, status 183
0000077c.000003dc::2007/10/12-04:09:56.652 INFO [Qfs] QfsOpenFile C:DOCUME~1SVC_CL~1LOCALS~1TempCLS34D5.tmp => 3, 36cf960 status 0
0000077c.000003dc::2007/10/12-04:09:56.652 INFO [Qfs] QfsOpenFile Q:MSCS 592a048-5457-426a-a46d-996441ef0182 0000004.CPT => 2, 36cf8d0 status 183
0000077c.000003dc::2007/10/12-04:09:56.652 INFO [Qfs] ReadFile 928 (regf) 32768 16384, (0=>0) 0 status 0
0000077c.000003dc::2007/10/12-04:09:56.652 INFO [Qfs] WriteFile 7e0 (regf) 16384, status 0 (0=>0)
0000077c.000003dc::2007/10/12-04:09:56.652 INFO [Qfs] ReadFile 928 (regf) 32768 0, (0=>0) 0 status 0
0000077c.000003dc::2007/10/12-04:09:56.652 INFO [Qfs] QfsFlushBuffers 7e0, status 0
0000077c.000003dc::2007/10/12-04:09:56.652 INFO [Qfs] QfsCloseHandle 928, status 0
0000077c.000003dc::2007/10/12-04:09:56.652 INFO [Qfs] QfsCloseHandle 7e0, status 0
0000077c.000003dc::2007/10/12-04:09:56.652 INFO [Qfs] QfsDeleteFile C:DOCUME~1SVC_CL~1LOCALS~1TempCLS34D5.tmp, status
You can use a Select top @variable in sql server 2005:Thus:--'Throttle' the result set:Select Top (@MaxBatchSize)KeyID, LId, ArrivalDt, CaptureDt, Lat, LongFrom GPSDataOrder By CaptureDt DescMakes messing with Set Rowcount *so* redundant :-)
View 1 Replies View Relatedhi ..
i made an application using vb6 that access the table in a sqlserver database and in the load event i write
cn.mode=adsharedenyread
and i make two copies of the application and run them both of them read the data and no deny happen !!! although both of the applications have adsharedenyread
how can i make privilage for some users to read or write ?
by the way is that can be done in ado.net 2.0 ?
thanks in advance.
After installing the March SQLExpress Advanced package, I noticed that SQLXML4 did not install.
Is there a way to install the proper version separately, or do I just need to reinstall the entire package?
Thanks,
Paul
--create a temp table like:
select * into #tmp from
(select 1 as ID
union all select 2
union all select 3
union all select 4
) as A
-then run this query:
select B.ID,isNull(B.GID,0) as GID from
(select #tmp.ID,A.GID
from
#tmp
left outer join
(select ID,1 as GID from #tmp where ID in (2,4)) A
on #tmp.ID = A.ID
) as B
--I thougth it should return:
10
21
30
41
--but it actually returned:
IDGID
11
21
31
41
--if I change GID value in '...(select ID,1 as GID from #tmp where ID in (2,4)...', for example 2, then it return:
IDGID
12
22
32
42
WHY?
Hi There
I have just done my first clean install of SQL 2005. I have previously upgraded several boxes from SQL 2000 without incident.
The clean install seemed to go fine but the Maintenance Tasks didn't install? I have looked on the web but have been unable to unearth any information. Has anyone encountered this before and if so I how do I add Maintenance Tasks to the Management folder?
Hi There
View 1 Replies View Related
I just noticed that SQLXML4 did not install when I installed SQLExpress Advanced package (Mar 2006).
Anyone know if I can install SQLXML4 separately for SQLExpress Advanced or do I need to uninstall and re-install the entire package?
Thanks,
Paul
Hi every body !
I try to insert the reportViewer in my asp page, all is good, but if I want to change (by choosing another report), and i cannot call the method RefreshReport() as I saw in the msdn documentation.
I didn't understand why, I download the ReportViewer from msdn download : the version is 2.0.50727.42 from 22/01/2006...
I implement in my asp designer tools the following dll : Microsoft.SqlServer.UpgradeAdvisor.ReportViewer.dll 9.0.1399.1 from 14/10/2005
Did I forget any code line ? or did I download a bad version ?
Code Snipp
ReportViewer1.ServerReport.ReportPath += "CounterReport";
ReportViewer1.Refre.... //Visual studio didn't propose the method !!
I created a SSIS package that imports a file then moves the file to a folder called processed.
I created a job that runs the package. The pack works fine when I run it from the SQL Storage Area. When I run the job I get the following message:
Started: 9:58:15 AM DTExec: The package execution returned DTSER_SUCCESS (0). Started: 9:58:15 AM Finished: 9:58:21 AM Elapsed: 6.047 seconds. The package executed successfully. The step succeeded.
showing that the job supposedly completed successfully.
I know that the package the not run because the data did not get imported and the file did not move.
Can anyone explain the package may not run but we still get a job completion status of success?
Hello, everyone:
I have a backup plan that remove the old backup files more than two days. Last Monday, the SQL Server restart. Since that, the backup is fine, but it didn't remove the old backup files. Why and how to handle that? Thanks
ZYT
I have a very weird problem.
I ran a SQL statement to get the data from two tables using join. These two tables have a column in the same name €œID€?.
SQL=€?select * from table1 a, table2 b where a.ID=b.ID€?
Set RS=DB.Execute(SQL)
The statement worked. But when I output the records in my ASP pages, I got an error:
ADODB.Recordset error '800a0cc1'
Item cannot be found in the collection corresponding to the requested name or ordinal€?
This is the line caused error:
<%= RS(€œa.ID€?) %>
It seemed that SQL Server recordset doesn€™t carry the table aliens. If I removed a. from ID the error was gone but it gave me the value of b.ID.
How to solve the problem? Thank you.
Dear all,
I have built an SSIS package by using the BI Dev Studio and enabled its configuration xml file.
The package have a variable called TranDate and I want to put it dynamically from a Calendar on my website (just like assigning a variable).
I have successfully change the value of that variable in the configuration files (affected to the xml file).
Then I loaded the package and executed it (through my web). It's still get the old value (which I have assigned while creating the package).
I didn't understand that where else can the package get the value of that variable so it still get the old value (that value have never appeared in the xml configuration file anymore when I changed it).
Thanks for reading this, and I am looking forward to seeing any helps from you guys.
Dear all,
I've been searching the article for error handling in SSIS but seems no article have same problem exactly as mine. In my package there's container, it contains Data Flow task and some of Script tasks. I put an error precedence from Data Flow task into Script task that contains script for log an error that might be occured. The Data Flow task imports data from flat file into SQL Server 2005 and I've set a semicolon as the column delimiter. Unfortunately there is a data in flat file that had one column contain semicolon as its value.
This would trigger an error and I hope the error would be logged into a table as I wrote inside Script task. But it didn't work. The error precedence won't work, the package stop in flat file source instead. I've been trying the event handler but it didn't work either. Maybe I got wrong implementation, anybody can help me explain the error handler and solve the problem ?
Here is the capture of my package, since I didn't know how to attach the picture in this forum.
Thanks in advance.
Best regards,
Hery
I am doing search through Date Range but didn't get correct result.
For Example if i created a Task on 5/8/2014 and when i have take Date Range from 1/8/2014 to 6/8/2014 then didn't get 5/8/2014 data in result but when i taken range from 1/8/2014 to 5/8/2014 then got the 5th Date data.
I am taking Range Inclusive Operator(:).
SELECT ( STRTOMEMBER(@FromDate) : STRTOMEMBER(@ToDate) )
Greetings,
I thought I had applied sp2 successfully but I discovered when I tried to apply the hotfix ( sqlserver2005-kb933097-x86-enu.exe), that the SQL Server Tools and Workstation Components wasn't at the 3042 level.
The recommendation in the log file is to:
"Update 3152 is dependent on prerequisite update 3042. Exit setup and refer to the Knowledge Base article to find the prerequisite patch."
Does anyone know where I find the prerequisite patch?
Any help would be appreciated.
Regards Noel
Time: 03/13/2007 18:05:39.045
KB Number: KB933097
Machine: NOEL_EVO
OS Version: Microsoft Windows XP Professional Service Pack 2 (Build 2600)
Package Language: 1033 (ENU)
Package Platform: x86
Package SP Level: 2
Package Version: 3152
Command-line parameters specified:
Cluster Installation: No
**********************************************************************************
Prerequisites Check & Status
SQLSupport: Passed
**********************************************************************************
Products Detected Language Level Patch Level Platform Edition
SQL Server Database Services 2005 (SQL2005) ENU SP1 2005.090.3042.00 x86 DEVELOPER
SQL Server Analysis Services 2005 (SQL2005) ENU SP1 2005.090.3042.00 x86 DEVELOPER
SQL Server Reporting Services 2005 (SQL2005) ENU SP1 9.00.3042.00 x86 DEVELOPER
SQL Server Notification Services 2005 ENU SP1 9.00.3042.00 x86 DEVELOPER
SQL Server Integration Services 2005 ENU SP1 9.00.3042.00 x86 DEVELOPER
SQL Server Tools and Workstation Components 2005 ENU SP1 9.1.2153 x86 DEVELOPER
**********************************************************************************
Products Disqualified & Reason
Product Reason
SQL Server Tools and Workstation Components 2005 The product instance SQL Tools does not have prerequisite update 3042 installed. Update 3152 is dependent on prerequisite update 3042. Exit setup and refer to the Knowledge Base article to find the prerequisite patch. Install the prerequisite and rerun the installation.
Hi,
i'm doing POS system by using C# in visual studio 2005. Recently, i installed the microsoft sql server management studio on my pc, i plan to do merge replication through local publications between my pocket pc and my pc. So, i opened the replication file which is under Object Explorer in microsoft sql server management studio, it ONLY show me the local subscription folder under the replication file.Do i install incomplete set of microsoft sql server management studio?Local publications and local subscription, which one is more suitable for POS system?
Trying to create a summary table of current product table, so that they is only 1 line for each product (even if the product is in both warehouses - in this case take table for warehouse 1) Therefore standard case statement becomes:
CASE WHEN exists (select product from stock where warehouse = '02' and product = stock.product and product in (select product from stock where warehouse = '01'))
ProductDescription = (select distinct long_description from stock where warehouse = '01' and product = stock.product)
ELSE
ProductDescription = (select distinct long_description from stock where product = stock.product and (warehouse = '01' or warehouse = '02'))
END
Is there another way of writting this, instead of repeating the above code for each column in the table?
THANKS
Hi,
Once again some doubt!
I have a query as below -
Select TeamName ,
SUM(CASE WHEN Team_Date = '01/07/2007' THEN Team_Total ELSE 0 END) AS [Jul 07] ,
SUM(CASE WHEN Team_Date = '01/08/2007' THEN Team_Total ELSE 0 END) AS [Aug 07] ,
SUM(CASE WHEN Team_Date = '01/09/2007' THEN Team_Total ELSE 0 END) AS [Sep 07] ,
SUM(CASE WHEN Team_Date = '01/10/2007' THEN Team_Total ELSE 0 END) AS [Oct 07] ,
SUM(CASE WHEN Team_Date = '01/11/2007' THEN Team_Total ELSE 0 END) AS [Nov 07] ,
SUM(CASE WHEN Team_Date = '01/12/2007' THEN Team_Total ELSE 0 END) AS [Dec 07] ,
SUM(CASE WHEN Team_Date = '01/01/2008' THEN Team_Total ELSE 0 END) AS [Jan 08] ,
SUM(CASE WHEN Team_Date = '01/02/2008' THEN Team_Total ELSE 0 END) AS [Feb 08] ,
SUM(CASE WHEN Team_Date = '01/03/2008' THEN Team_Total ELSE 0 END) AS [Mar 08] ,
SUM(CASE WHEN Team_Date = '01/04/2008' THEN Team_Total ELSE 0 END) AS [Apr 08] ,
SUM(CASE WHEN Team_Date = '01/05/2008' THEN Team_Total ELSE 0 END) AS [May 08] ,
SUM(CASE WHEN Team_Date = '01/06/2008' THEN Team_Total ELSE 0 END) AS [Jun 08]
FROM dbo.uView_DimHC_Team_Details_View1 where TeamParentID < '3' GROUP BY TeamName ORDER BYTeamName
This basically creates a table where I have team names as rows, month names as columns and team strength as each value.
Now I want to add one row at the bottom which gives a summary which basically calculates all the values in that column. I am displaying this on web where I can do this using gridview but the problem is I am using the same gridview for 2-3 different queries which have different columns so the rowdatabound method can not be used.
How can I get aggregate row at the end of table from this table?
Thanks
I have a 2.8G SQL 7 database with 1.6G used space. I want to shrink it to 1.8G, but after I issued the 'DBCC srhinkfile(datafile, 1800)', it didn't shrink the file, instead it expanded to 2.7G used space? I tried on a test server, the command worked every time although the time to complete the task tended to increase every time.
I did the DBCC DBREINDEX (fill factor 90) and updated usage, the size didn't change. DBCC CHECKDB, CATELOG AND SHOWCONTIG were all performed and there wasn't any error or fragmentation.
Now I'm wondering what's the actual size of my database, 1.6G or 2.7G? If there's a way that I can get back close to my original used sized, then I can try to shink the file again. Any comment is welcome. Thanks advance.