Can anyone tell me what is the maximum number of rows that I can code in a T-SQL step if I want to
include this in a job? I do not want to use stored procedure for certain task, so I want to put the same code in a T-SQL step.
Thanks!
Sheila.
Hi, I was trying to run an insert statement which has subquery, But it is returning the error like this..
Subqueries are not allowed in this context. Only scalar expressions are allowed.
--------------
Is there a way to reparse the insert statement without have to assign the subquery to a temp value and insert it?
thanks..
Here is my SQL..
insert into admincriteria values (nextID,(select id from nodetable where description like 'Example - Quality Analytics'),8071,2,'Failures by Customer',2,0,0)
Hi All,
Please let me know where can i find the sample files and rs2005sbsDW database in msdn library as i dont have CD provided along with the book.
Please give me the link of the sample files so that i can download it for testing the sample application.
Regards,
Prabhanjana
is there such a paper? if so, can you pls point me to it? thx in advance
View 4 Replies View Relatedhye everyone,
after finished do the report and deploy at IIS/report manager in local pc..
i want to know , what is the step /how to deploy the report project at the user's pc/ client pc /other server..
thanks in advance
thank you very much
I want to convert .rdlΒ to .rdc need full steps.Actually i created .rdl report using sp sucessfully.Now i want to convert it to rdlc while doing it iam getting some authentication error and some thing else.I created rdl in 2008 and i want to change it to rdlc 2010.
View 5 Replies View RelatedI have a package that has multiple data flow tasks. At the end of a task, key data is written into a raw file (file name stored in a variable) that is used as a data source for the next task. Each task requires a success from the preceding task.
Here's the rub:
If I execute the entire package, the results of the package (number of records of certain tasks) differs significantly from when I execute each step in the package in turn (many more records e.g. 5 vs 350).
I get the feeling that the Raw file is read into memory before it is flushed by the previous task, or that the next task begins preparation tasks too early.
Any help is greatly appreciated.
I am running on Server 2003 64 (although the same thing happens when deployed on a Server 2003 32 machine)
Thanks
B.
Hi!
I hope the answer is as simple as the question -- but after reading all the documentation I could find (understand?) and a lot of posts here, I'm no closer to achieving the goal.
I have a Visual C# app, DAYTRACKER, developed in VS2005. It uses a database with several tables constructed using SQL Server 2005 Developer Edition.
I want to deploy the app plus the database plus SQL Express to another machine, to be used by a single user (the administrator) with no need for network connectivity of any kind.
What I have so far is:
1. The application is successfully deployed from a CD-ROM, having used the Publish process within VS2005, and opens on the new machine -- without database connectivity, however.
2. SQL Express is successfully deployed (it deployed as a 'prerequisite' when I went through the Publish process in VS2005)
3. I manually copied the database's .mdf and .mdl files, using SQL Server Managers 'Copy Database' function, then transferred the copies to the new machine into the ..MSSQL.1MSSQLdata folder (where they appear along with the master.mdg, mastlog.ldf etc files)
Now, the DAYTRACKER application's DAYTRACKERConnectionString under 'Settings' in the VS2005 studio reads 'Data Source=DELL3;Initial Catalog=DayTracker;Integrated Security=True' (which are the appropriate parameters for the machine, DELL3, on which I wrote the program.)
The problem, of course, is that SQL Express on the new machine doesn't connect the application to the database. When I go to the 'SQL Server Configuration Manager' and go to the 'SQL Server 2005 Services' and double-click on the 'SQL Server (SQLEXPRESS)' icon (the service is running) and the user is logged on using 'Local System Account'. Under the 'Service' tab the Host Name is 'MUSIC' (which is the name of the new machine I've installed the app onto -- which of course is not the name - DELL3 - that the app's connection string is expecting). Under the 'Advanced' tab, I've tried correcting the name of the Startup Parameters default .mdf and .mdl entries to ..DayTracker.mdf and ..DayTracker_log.mdl, but the server won't start up after I make the changes.
What I'm hoping for: a step-by-step way of doing this type of deployment, preferable getting it all onto one CD-ROM, and installing it on the new machine so that it all works seamlessly from the start, not requiring any 'tweaking' of the SQLServer Express settings by the end-user.
But I'll take pretty much anything that fixes the specific db connectivity problem I've described.
Thank you very much.
John F.
Hi,
I have to transport a big database table and can't read it at once with "select * from table" because the table is bigger than my system memory.
Is there a way to read the table step by step? I thought it was possible with ADO and his serverside cursors but I don't now how. I need an "universal" solution that works on SQL Server 2000/2005, MySQL and Oracle.
Regards,
harry
Connecting to a networked SQL Server Box from my local machine
Open Query Analyzer from Start menu, logging in using sa account.
from the object browser i select my stored procedure (WEA_InsertClaim) - right click and select Debug.
i am prompted to enter the parameter values, which i do, auto rollback checkbox is checked - click Execute.
T-SQL Debugger opens and runs through the stored procedure.
but only buttons enabled are the "Go", "Toggle Breakpoint", "Clear All Breakpoints"
so i can set breakpoints etc. but when i select Go it will not stop at the breakpoints it just runs through the stored procedure from start to finish. giving the correct return code as its output
is there something i need to enable in order to make it stop at breakpoints??
Cheers,
Craig
Is there a good step by step guide to setting up an indirect configuration? I've followed the steps in Kirk Haselden's 'Keep your packages in the dark' article 3 or 4 times this afternoon and nothing seems to work. I cannot even get the XML configuration file to work by accessing it directly. I'm using the wizard to create the file.
Just wondering if there was something else out there....
Thanks in advance for any help.
MarkA
Hi
I posted this on the sql server security forums too. Here is the error that i get when i change the type of the step to ssis
Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)
Additional information:
An exception occured while executing a Transact-SQL statement or batch.
(Microsoft.SqlServer.ConnectionInfo)
The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction
The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction
The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction
The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction
The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction
The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction
The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction
The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction
The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction
The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.(Microsoft SQL Server, Error: 3930)
This error pops up right after i change the type of the step to "SQL Server Intergration Services Package"
I have made the following configurations:
The user group (windows group) that the user belongs has the following roles in msdb :
db_dtsadmin
db_dtsltuser
db_dtsoperator
SQLAgentOperatorRole
SQLAgentReaderRole
SQLAgentUserRole
i have made a proxy to sql server agent which has the following subsystems :
"SQL Server Integration Services Provider" the proxy is tied to the same login which has those SQLagent and dts roles in msdb database.
Im using windows authentication and the user that logs into the sql server is in the same group that i have set all of the rights.
Ps. Clearly im missing some role or right somewhere because as soon as i give the group sysadmin role then all the users in that group can create SSIS steps in the agent.
Ps. Ps. I have been living under the impression that i dont have to give sysadmin rights to people that create ssis packages and schedule then with the agent.
I am trying to learn Reporting Services using the title "MS SQL Server 2005 Reporting Services Step by Step" by Stacia Misner and Hitachi Consulting, published in 2006. I am experiencing problems with some of the exercises. I got as far as Chapter 4 when I followed directions to create a SQL statement to define a query string for a dataset. Pg 80:
select * from vProductProfitability
where year = 2003 and
MonthNumberOfYear = 1
The view vProductProfitability does not exist in the tutorial database that came with the book, rs2005sbsDW. The result of this query is the basis for the entire chapter on developing basic reports and I'm being denied a learning opportunity because the view does not exist. In short, I'm stuck.
I have tried to find somewhere at Microsoft to place this question and get some answers so I can continue thru the tutorial. To no avail. Does anyone have any suggestions?
BTW, the solution that came in the CD is also wrong because the query noted above is also in the solution.
This humble grasshopper seeks wisdom.
Respectfully Submitted,
Dave Matthews
Atlanta, GA
aka FlooseMan Dave
Dear Experts,
please guide me for replcation.....
i'm using sql server 2005 server tools developer edition. my OS is professional 2000.
and i've sqlserver 2000 client tools also
i've two databases in my machine. publisher is in different instance, and the transactions might be maximum 50 per day. my aim is when ever developer enters the data into the main database, automatically it should be updated on the my two databases also.
i mean the server is sysA and the database is srtp.
my machine is sys20 and the databases are srtp1, srtp2.
how should i make sysA as publisher?
i've right click on the server databases, but it is showing newsubscriptions option only.
Vinod
Even you learn 1%, Learn it with 100% confidence.
Visual C# 2005 Step by Step John Sharp
Intel D975XBX 930 2GB ram HD 300GB - MatroxVD
Windows XP Sp2 + updates
Visual Studio 2005 Professional edition
SQL Sever 2005 Standard Edition
20070203
Install Practice Files at D:Program FilesMicrosoft PressVisual C Sharp Step by Step.
Configuring SQL Sever Express Edition
Hostname - xxxxxxxxx..
At sqlcmd s xxxxx...SQLExpress E got - Pipes error.
Open Microsoft SQL Sever 2005 - Configuration Tools SQL Configuration Manager select Protcols for SQLEXPRESS select Named Pipes enable close.
At sqlcmd s xxxxx..SQLExpress E get Sqlcmd: : Unknown Option. Enter -? for help.
Chdir C:Documents and SettingsAll UsersStart MenuProgramsMicrosoft Server 2005
Sqlcmd s xxxxxx..SqlExpress E
Hresult 0x2, Lvel 16, State 1
Named Pipes Provider: Couldnot open a connection to SQL Server [2].
Sqlcmd: Error: Microsoft SQL Native Client : An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections..
Sqlcmd: Error: Microsoft SQL Native Client : Login timeout expired.
How do I fix this?
Need to be able to to use the Northwind Traders data base
Thanks
Friends -
Could any one of you provide steps for upgrading SQL Server 2000 cluster to SQL server 2005 cluster.
My environment is Windows 2003 server.
Appreciate your support.
Cheers
VSH
I have a sql select statement, then I have a datareader and I'm trying to loop through that reader and insert a record into another table each time until there are no more records in the loop. I'm not sure how to do this though without using 2 datareaders. Please help. Thanks
1 ' Retrieve Data from database based on selections chosen in ListBox
2 Dim cmdCommittee As New SqlCommand("Select * from committees_tbl where committee_id in" & _
3 "(" & strCommitteesRemoveLast & ") order by committee_name", conn_Insert)
4
5 ' setup a datareader
6 Dim drCommittee As SqlDataReader = cmdCommittee.ExecuteReader()
7
8
9 ' Loop through datareader and insert rows
10 ' into the xref_person_committees_tbl
11 While drCommittee.Read()
12 Dim strCommitteeName As String = drCommittee("committee_name") 'retrieve committee_name from datareader
13
14 ' Create a sql string
15 Dim strAddCommittee As String = String.Empty
16 strAddCommittee = "Insert into xref_person_committees_tbl (committee_name) values ('" & strCommitteeName & "')"
17 'Response.Write(strAddCommittee & "<br>")
18
19 ' Create a sql command to process the insert
20 Dim sqlAddCommittee As New SqlCommand(strAddCommittee, conn_Insert)
21 Dim drNewCommittee As SqlDataReader = sqlAddCommittee.ExecuteReader()
22
23
24
25
26 End While
27 ' -----------------------------------------------------------------------------------
28
29 drCommittee.Close()
I want to pass the string "select @@servername" in the print statement, it throws error as:=
Msg 1046, Level 15, State 1, Line 3
Subqueries are not allowed in this context. Only scalar expressions are allowed.
Msg 1046, Level 15, State 1, Line 28
Subqueries are not allowed in this context. Only scalar expressions are allowed.
declare @account_name nvarchar(400) = 'Mail Account';
IF exists (select * from msdb.dbo.sysmail_account where name = @account_name)
PRint 'Database Mail Account ' + quotename (@account_name) + 'is already setup in the Server:= ' + (select @@servername);
I mean why such restriction? In such queries results will be only 1 so it does not violate the SET operation. correct??
I want to put some trace in the a UDF, so I put print in the function. IT gave error. Can anyone please explain why this happen. But this work with SPs.
Cheers
Shimit
Hi
Keep getting this message in the Application Event Viewer.
Source: SQLAgent$SHAREPOINT
Category: Alert Engine
Event ID: 324
Description: SQLAgent is not allowed to run.
Can't find any help online. Anyone out there got any ideas?
TIA
Hi everyone,
I was browsing and came across this code with this result set
CREATE TABLE dbo.SalesByQuarter
(
Y INT,
Q INT,
sales INT,
PRIMARY KEY (Y,Q)
)
GO
INSERT dbo.SalesByQuarter(Y,Q,Sales)
SELECT 2003, 2, 479000
UNION SELECT 2003, 3, 321000
UNION SELECT 2003, 4, 324000
UNION SELECT 2004, 1, 612000
UNION SELECT 2004, 2, 524000
UNION SELECT 2004, 3, 342000
UNION SELECT 2004, 4, 357000
UNION SELECT 2005, 1, 734000
GO
SELECT Y,
[1] AS Q1,
[2] AS Q2,
[3] AS Q3,
[4] AS Q4
FROM
(SELECT Y, Q, Sales
FROM SalesByQuarter) s
PIVOT
(
SUM(Sales )
FOR Q IN ([1],[2],[3],[4])
) p
ORDER BY
GO
DROP TABLE dbo.SalesByQuarter
GO
Y Q1 Q2 Q3 Q4
2003 NULL 479000 321000 324000
2004 612000 524000 342000 357000
2005 734000 NULL NULL NULL
I tried to modify it to remove the nulls by changing this line of code
SUM( ISNULL(Sales,0))
I got this error
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'ISNULL'.
so i tried it like this
and got this error
Msg 195, Level 15, State 1, Line 12
'ISNULL' is not a recognized aggregate function.
My question is why can't i use isnull to change NULL TO 0
Thanx
Slimshim
Could someone please tell WHY it is not allowed to use the following construction in a stored procedure!?
AS IF @taxparent = 0 THEN SET @taxparent = NULL IF @museum = 0 THEN SET @museum = NULL IF @collection = 0 THEN SET @collection = NULL SELECT ID, SpecimenNr, ScientificName, Locality, Taxon FROM QueryView
I get the following error messsages:
Msg 156, Level 15, State 1, Procedure DynamicQuery, Line 9Incorrect syntax near the keyword 'THEN'.Msg 156, Level 15, State 1, Procedure DynamicQuery, Line 10Incorrect syntax near the keyword 'THEN'.Msg 156, Level 15, State 1, Procedure DynamicQuery, Line 11Incorrect syntax near the keyword 'THEN'
Any help is greatly appreciated!
I am using the SQLEXPRESS database, and connect from to locations:
Windows Service
ASP.NET web application.
whenever I start the windows service I get the error: Cannot open user default database. Login failed.
so I am going to visual studio and close the connection. then I close the web browser, and the windows service succeed connect to database.
but then, after windows service is connected, I open the web browser application and fails to login because the login system using the same database of windows service, and since the connection is being used by windows service, login is impossible.
Do you know how I can allow 2 connections at the same time for the same database?
what are the risks to do that? and how do I handle this risks.
thanks alot
Hello,
I need to know what kind of characters are NOT allowed in SQL literals and the way(s) to go around it.
In example:
Code:
' must be replaced by ''
I would greatly appreciate all the help you guys can provide me with.
Thank you for taking the time.
Hi all,
I need to rewrite the following query without a nested query (stupid mysql 4.0.25. Can anyone lend a hand?
SELECT name from `list` where name not in (
SELECT DISTINCT b.name from ` armor ` a,` list` b where b.name = a.name
);
I entered the max 8000 varchar in a column and it will not let me enter more than about 1000. What is the deal?
thanks
Matt
Um, still trying to transpose MySQL into T-SQL.
Inserting info into a table, where one of the columns meets a certain criteria.
insert into employee (/*emp_id,*/ fname, lname, start_date,
dept_id, title, assigned_branch_id)
values (/*null,*/ 'Michael', 'Smith', '2001-06-22',
(select dept_id from department where name = 'Administration'),
'President',
(select branch_id from branch where name = 'Headquarters'));
But I'm getting this error:
Msg 1046, Level 15, State 1, Line 5
Subqueries are not allowed in this context. Only scalar expressions are allowed.
Any help would be greatly appreciated.
For example I have
CASE (a.t_id)
WHEN (a.t_id in (22,23,27,30,38))
THEN t.desc
ELSE 'N/A'
END 'Column name..',
and that is giving me "incorrect syntax near 'in'" ??
I am trying to make the following SQL statement, but there seems to a limiton how long a statement can be:INSERT INTO CUSTOMER (forename, surname, company_name, title, addressA,addressB, postal_number, city, country, home_phone, mobile_phone,work_phone, fax, email, sale_procentage, bank, account_number,creation_initials, creation_date, creation_reason) values ("test", "test","test", etc...);But I can only enter this much text:INSERT INTO CUSTOMER (forename, surname, company_name, title, addressA,addressB, postal_number, city, country, home_phone, mobile_phone,work_phone, fax, email, sale_procentage, bank, account_number,creation_initials, creation_date, creation_reason) vaIs there some upper limit? And how do I make a long SQL statement like this?JS
View 1 Replies View RelatedI have a simple table, for some reason, certain columns seem to acceptNulls even though they shouldn't, for example the I can set the 'Name'field to Null using my web application or directly in EnterpriseManager. field How do I prevent this? However the 'RecCreated' doessnot permit nulls.CREATE TABLE [dbo].[Group] ([GroupID] [int] IDENTITY (1000, 1) NOT NULL ,[Name] [nvarchar] (50) NOT NULL ,[Description] [nvarchar] (750) NULL ,[RecCreated] [datetime] NOT NULL ,[RecUpdated] [datetime] NOT NULL ,[RecCreatedBy] [int] NOT NULL ,[RecUpdatedBy] [int] NOT NULL ,[RecActive] [int] NOT NULL) ON [PRIMARY]GOthanks for any help you can give on this
View 7 Replies View Related
Working on partitioning a few large tables. One of the tables included a text column and the TEXTIMAGE_ON [PRIMARY]? clause which would prevent the partitioning of this table. After some research we found that the data was legacy and no longer used. We updated the column on the affected rows to NULLS and altered the column to a VARCHAR(20)
I then attempted to run the ALTER TABLE SWITCH and I encountered the error
Msg 4947, Level 16, State 1, Line 1
ALTER TABLE SWITCH statement failed. There is no identical index in source table 'LocalDeltanet.dbo.testresultsjoe' for the index 'PKIDX_testSummary' in target table 'LocalDeltanet.dbo.testresults_part'.
After a lot of grief and testing I determined that the message was bogus and the real issue is that the 'sys.tables' still has lob_data_space_id? with a value of 1 for this table.
I created a copy of the table with the text column and the "TEXTIMAGE_ON", then altered the column to a varchar and another table with just the varchar column and no "TEXTIMAGE_ON" spoecified. After copying the data from the original table, I tried to run the Alter Switch. It failed once again for the table with the text column that was altered to varchar, but it worked for the table that had the column specified as varchar from the start.
All other things have been checked and the two source tables in this test are identical execpt for the Text column specification. The alter column changes the definition of the column, but how would you remove the lob_data_space_id? setting, since it appears that this value is causing my issues, is there anyway to update the table in place. I know I can BCP the data out, but that would take too long and would defeat the advantage of using the alter switch method.
BOL States:
The allow updates option is still present in the sp_configure stored procedure, although its functionality is unavailable in Microsoft SQL Server 2005 (the setting has no effect). In SQL Server 2005, direct updates to the system tables are not supported. This means we cannot update the table manually.
Hi, I've read that a SQL Server Express database is limited to GB. If I have a database with about 120 tables which store only simple datatypes (int, money, datetimes, varchar, etc.), no blobs or large files, how can I estimate the amount of data or number of rows it can hold before it runs out?
View 11 Replies View RelatedJust installed SQL Express but cannot connect since it says Remote Connections are not allowed ! This worked before. I have tried different protocols and even SQL Authentication. No luck here and I've been up all night...
View 5 Replies View Related