SMO Problem / Can't Select Non System Views Without Crash
Mar 5, 2006
I've had a couple problems with SMO and I wonder if anyone else has. Specifically, when I run through stored procedures (just like your example) it is painfully slow. Maybe 5 a second. Another problem is filtering on nonsystem objects with either "SVR.SetDefaultInitFields(typeof(Microsoft.SqlServer.Management.Smo.View), "IsSystemObject"); or View.isSystemObject.
Both cases crash my app. Any ideas?
Below are two ways that both crash or hang.
SVR.SetDefaultInitFields(typeof(Microsoft.SqlServer.Management.Smo.View), "IsSystemObject");
foreach (Microsoft.SqlServer.Management.Smo.View view in viewCollection)
{}
OR
foreach (Microsoft.SqlServer.Management.Smo.View view in viewCollection)
{
if (!view.IsSystemObject)
{}
}
View 1 Replies
ADVERTISEMENT
Nov 28, 2006
Hello,
From time to time, our production system crashes and reboots while running some data warehouse load packages. There is nothing in the logs; it just seems like the reset button is pushed. The situation occurs at different moments in the batch and not every night, but lets says once every two weeks or more frequent.
We are running Windows 2003 server and SQL Server 2005 SP1 incl. hotfixes.
Does anyone of you have this issue, or is this a hardware issue without any doubt? Could there be any chance it's in the SQL Server software?
Regards, Jeroen
View 3 Replies
View Related
May 25, 2008
All- Please advise how to configure a gridview so that when introducing non-editable fields to the select statement, the edit function doesn't crash with a "Procedure or function (UpdateCommand) has too many arguments specified."
The scenario: Update works find when I have a gridview based on the following SPs:
SELECT:
1 ALTER PROCEDURE dbo.H2SelectCommand
2 AS
3 SET NOCOUNT ON;
4 SELECT headcount_id, person_id, act_session_id, no_answer
5 FROM headcount
6
INSERT:
1 ALTER PROCEDURE dbo.H2UpdateCommand
2 (
3 @person_id smallint,
4 @act_session_id smallint,
5 @no_answer bit,
6 @Original_headcount_id smallint,
7 @headcount_id smallint
8 )
9 AS
10 SET NOCOUNT OFF;
11 UPDATE [headcount] SET [person_id] = @person_id, [act_session_id] = @act_session_id, [no_answer] = @no_answer WHERE (([headcount_id] = @headcount_id));
12
HOWEVER, when I alter the select command so that it includes a column (person.person_name) from a parent directory like this:
1 ALTER PROCEDURE dbo.H2SelectCommand
2 AS
3 SET NOCOUNT ON;
4 SELECT headcount_id, person.person_name, headcount.person_id, act_session_id, no_answer
5 FROM headcount
6
7 INNER JOIN person
8 ON headcount.person_id = person.person_id
...and then proceed to do an Edit/Update operation, I get the pesky "Procedure or function H2UpdateCommand has too many arguments specified" message.
The only obvious thing I could think to try was to configure the new column in the gridview to be Read Only, but this didn't help. Do I have to alter the Update SP to somehow account for the new field, even though the warning already says that there's too many arguments in it?
Any ideas on how to fix this error would be appreciated!
-Kurt
View 3 Replies
View Related
Apr 10, 2007
I was looking in sys.syslogins and sys.sql_logins for a property to tell me if the login is locked but all i see are properties for if the account is enabled or if the account has access.
Is there a system view I can query to tell if if the account is locked ?
Thanks,
View 3 Replies
View Related
Jan 31, 2006
Hello,
can someone tell me where I can look to find a sql command that
is being executed by an application against my database?
In Oracle, I look in the v$sql_text view - is there something
similar in SQL Server 2000 ?
Many thanks in advance
View 2 Replies
View Related
Nov 26, 2007
I'm trying to access a System Catalog View (sys.database_permissions, and others) from a table valued function. I need to be able to pass the database name into the function so that I can operate on any database. Typically I'd use dynamic SQL to do something like
INSERT INTO #tempTable
EXEC ('SELECT * FROM ' + @DBName + '.sys.database_permissions')
But of course I can't use dynamic SQL inside of a UDF. I know I could do this using a stored procedure, but I'd need the output to be a recordset that I can query.
Has anyone done anything like this? I think I'm stuck.
View 5 Replies
View Related
Jun 4, 2008
Hi all, Is there any easy way to not allow a user to see system views? I have set up 1 view for a login, and I have to use an ODBC connection to access it. However, the 3rd party application I'm using is apparently timing out because of the number of tables/views that are returned. TIA! - Mark
View 2 Replies
View Related
Apr 30, 2007
Is there anyway to access system views on/from a linked server?
I have unsuccessfully tried various permutations of
select *
from [MDEDATAWTDss2005].master.[information_schema.colums]
Thanks
View 6 Replies
View Related
May 11, 2015
I got a script, which ran on SQL Server 7. After compiling on SQL Server 2012, I came across that the previous script used a system ViewsTables columns named "suid", which no longer operate in SQL Server 2012. After modifying the script with 2012 and replaced suid by SID, it works but the suid column is not appearing in System Views. What if we want to get that column back in SQL Server 2012? is there any possible way?
View 3 Replies
View Related
Mar 15, 2008
Please, could anyone tell me how to get information from which attribute of which table is the attribute from the view derived(it could also be a complex expression, not just attribute)through querying system tables or views(INFORMATION_SCHEMA, preferably if possible, because it's standard)
i'm using MSSQL2005.
Thank you!
View 4 Replies
View Related
Feb 4, 2008
As im diving into my new DBA role and reading as much as I can, I am a little confused on DMV's and system tables.
I've been reading through the book "SQL Server 2005 Bible", which has been very helpful. I tend to use it in conjunction with BOL whenever I come across something I want to learn more.
Last Friday, I tinkered around with DMV's, which was really cool, but I ran into something today that confused me.
Basically, it was finding out the recovery model for all the DB's on the server. The code in the book was:
SELECT [name], recovery_model_desc
FROM sys.databases;
Which turned exactly what it says it will.
however, I am confused.
I initially thought I needed to specify something in the "[name]" section, but realized, that is not the case.
My question is, why is that?
How do I know when to use [] around something?
I found this article:
http://www.databasejournal.com/features/mssql/article.php/3587906
I have been reading it.
I guess I am just really young and raw to T-SQL to know when to use the language corectly.
Is it due to the fact that the rules or syntax is a little different when using system tables?
Hope that makes sense.
Thanks.
TCG
View 5 Replies
View Related
Sep 21, 2006
All of a sudden none of our merge replications are working. In fact you can't even insert, update or delete and data from the tables in the merge publication. When trying that, we get an error stating:
Msg 550, Level 16, State 1, Procedure MSmerge_ins_E3F43EF8B259476099BBB194A2E1708C, Line 42
The attempted insert or update failed because the target view either specifies WITH CHECK OPTION or spans a view that specifies WITH CHECK OPTION and one or more rows resulting from the operation did not qualify under the CHECK OPTION constraint.
The statement has been terminated.
Currently, the only solution I've found is to delete the publication and recreate it. I'm trying to figure out why this happened. It happened on a development server that to my knowledge, hasn't been changed in a week or so outside of changing the server's IP address. Would that cause such an error to occur?
-mike
View 5 Replies
View Related
Oct 8, 2007
I'm recreating many of my DBA scripts that no longer work in 2005 due to the rework of system tables. It's a risk I lived with knowing that someday the system tables would change. I'm now encountering collation problems, which I do not understand. I know how to fix the problem, but I don't know why the collation issues exist in the first place.
Run the following command.
Select * From sys.all_objects a JOIN master..spt_values b on a.type = b.type
You will receive the following error.
Msg 468, Level 16, State 9, Line 1
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS_KS_WS" in the equal to operation.
Now run sp_help 'sys.all_objects' and look at the collation defintion for columns "type" and "type_desc". In my environment they have a collation of Latin1_General_CI_AS_KS_WS. This is different then the overall default collation of SQL_Latin1_General_CP1_CI_AS, thus causing the error.
My question is why did Microsoft need to make this collation different for these columns?
Any ideas?
Thanks, Dave
View 3 Replies
View Related
Sep 12, 2006
We are in the process of supporting two databases (Oracle 10g, SQL Server 2005) for our application.
I want to know what is the equivalent Tables/Views in SQL Server for the Oracle System tables dba_tab_comments, dba_tab_cols
Thanks in advance
View 4 Replies
View Related
Apr 25, 2008
Is this possible?
here is the situation. I have a DB on one system. I back it up and then restore it to a second system. This second system I run reports off of and I want to create custom views that do not exist on the original system. Can I restore the
backup DB from the remote system without wiping out the custom views on the local system?
I have to do this this way as they won't let us create the views we want on the remote system so the only way we have access to run the reports is by restoring the backup locally.
TIA!
View 2 Replies
View Related
Oct 19, 2006
I am trying to put the data from a field in my database into a row in a table using the SQLDataSource.Select statement. I am using the following code: FileBase.SelectCommand = "SELECT Username FROM Files WHERE Filename = '" & myFileInfo.FullName & "'" myDataRow("Username") = CType(FileBase.Select(New DataSourceSelectArguments()), String)But when I run the code, I get the following error:Server Error in '/YorZap' Application. Unable to cast object of type 'System.Data.DataView' to type 'System.String'. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.InvalidCastException: Unable to cast object of type 'System.Data.DataView' to type 'System.String'.Source Error: Line 54: FileBase.SelectCommand = "SELECT Username FROM Files WHERE Filename = '" & myFileInfo.FullName & "'"
Line 55: 'myDataRow("Username") = CType(FileBase.Select(New DataSourceSelectArguments).GetEnumerator.Current, String)
Line 56: myDataRow("Username") = CType(FileBase.Select(New DataSourceSelectArguments()), String)
Line 57:
Line 58: filesTable.Rows.Add(myDataRow)Source File: D:YorZapdir_list_sort.aspx Line: 56 Stack Trace: [InvalidCastException: Unable to cast object of type 'System.Data.DataView' to type 'System.String'.]
ASP.dir_list_sort_aspx.BindFileDataToGrid(String strSortField) in D:YorZapdir_list_sort.aspx:56
ASP.dir_list_sort_aspx.Page_Load(Object sender, EventArgs e) in D:YorZapdir_list_sort.aspx:7
System.Web.Util.CalliHelper.EventArgFunctionCaller(IntPtr fp, Object o, Object t, EventArgs e) +13
System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e) +45
System.Web.UI.Control.OnLoad(EventArgs e) +80
System.Web.UI.Control.LoadRecursive() +49
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +3743
Version Information: Microsoft .NET Framework Version:2.0.50727.42; ASP.NET Version:2.0.50727.210 Please help me!
View 3 Replies
View Related
Apr 15, 2006
People are telling me it is bad to putselect * from <atable>in a view. I better should list all fields of the table inside thedefinition of the view.I dont know exactly why but some say:A select * from makes sql server does a table scan.Is that true, even if i put a where on the select on the view? And what if idont list all fields in the select on the view?Thanks for the answer.Peter
View 33 Replies
View Related
Aug 16, 2007
Hi All,
Iam having 2 tables say Emp table and Department Table. Iam having a view which joins 2 tables.
example
select *,Department.DeptStatus, Department.DepartmentName from Employee INNER JOIN Department ON Department.deptno=Employee.DeptNO.
When I add a new column to the Employee table say EmployeeStatus then my view get corrupted.what i mean is that the last newly added column from emloyee table takes the Name as DeptStatus and shows values of the EmployeeStatus Column. We have been creating new Columns in SQL 2000 we never faced this issue but iam seeing this issue only in SQL Server 2005.
Thanks in Advance.
cheers,
Sada
View 7 Replies
View Related
Aug 2, 2007
Hai, I'm an novice in Sql Server. I wanna know why we are going for VIEWS instead of SELECT statement in Sql Server 2005. However, both are going to perform the same task (i.e, Retreiving data) from the database.
View 5 Replies
View Related
Dec 4, 2007
Hello,
How do , I grant select privileges on all the Views of the database to a user in SQl 2005. All the tables and views in the database are under the dbo schema.
Thanks.
View 1 Replies
View Related
Sep 9, 2007
Is it better to use just a SELECT statement or a view when working with a small database? Does anyone know which would perform better?
............................................................thanks
View 12 Replies
View Related
Jul 9, 2006
Hi,
I want to be able to select data from a database without using grid
view etc. I can set up a connection and bind it to a grid view but I
would like to do some data processing without showing it. Something like
select id from table where name = "x"
store id in idvar
store the row count to a variable rowcount
if rowcount > 0
select name from table2 where id = idvar
endif
etc
I can see how to set the sqldatasource select command but not how to run it and get results out of it. Please help
View 1 Replies
View Related
Apr 6, 2000
VIEW behaviour depending on what operation is run on it.
VIEW syntax is CREATE VIEW xx AS SELECT ... It would be very convinient to know wether a select, update, insert or delete is the operation executed on it so the view could take action accordingly, ie
CREATE VIEW xxx AS SELECT ..
CASE UPDATE THEN do_this
CASE SELECT THEN do_that etc.
Does anyone know if this is possible?
View 2 Replies
View Related
Mar 17, 2008
If I have a view such as: SELECT T.* FROM T
When I add a column to table T the view is not updated to reflect that change.
Furthermore, if there are other columns after the * in the view (for example SELECT T.*, GETDATE() as "My Date" FROM T) the last columns will contain incorrect data.
Is there a work around for this? An "auto-recompile when tables are modified" kind of option?
Thanks
Nick
PS: This is the script I used for testing:
create table tt (
test1 int primary key,
test2 int)
go
insert into tt (test1, test2) values (1,2)
go
create view vw_tt as select *, getdate() as "My Date" from tt
go
select * from vw_tt
go
create view vw_tt2 as select * from tt
go
alter table tt add test3 int
go
select * from vw_tt
select * from vw_tt2
select * from tt
drop table tt
drop view vw_tt
drop view vw_tt2
View 9 Replies
View Related
Dec 14, 2011
grant SELECTÂ Â on all_columns to yogi_bear;
grant SELECT  on all_objects to yogi_bear;
grant SELECT  on all_views   to yogi_bear;
Â
but it doesn't work, why? I know how to do it with a cursor or dynamic sql, but why won't these sql statements work?
View 7 Replies
View Related
Feb 1, 2007
I am building a dashboard features that allows user to select reports from a dropdownlist. It is pulling from a table called Reports (cols: ReportID, Description, sqlView) In this Report table the report is associated to a view that queries the report.
And the user's selections are stored in table called UserReport (cols: userID, ReportID, createDt) .
I need to get a Dataset to contain datables of all reports selected. (for example a user select 3 reports, the dataset should contain 3 datables that represent the report).
I want to accomplish this by create a store procedure that queries the Reports table and then dynamically executes the views that related to the user selected reports. Can anyone give me an example on how to create the storeprocedure?
Thanks,
CG
View 3 Replies
View Related
Sep 24, 2013
I'm trying to grant a user group select access to all the views in a database. I already made a query which creates a result set whit the SQL Syntax I (displayed below) need but it seems to be impossible to get this result set executed after creation.
use [AdventureWorksDW2008R2]
SELECT
'GRANT SELECT ON [' + SCHEMA_NAME(Schema_id) + '].[' + name + '] TO [DOMAINGROUP]'
FROM sys.views;
View 5 Replies
View Related
Jul 20, 2005
Hi all,We have an application through which we are bulk inserting rows into aview. The definition of the view is such that it selects columns froma table on a remote server. I have added the servers usingsp_addlinkedserver on both database servers.When I call the Commit API of oledb I get the following error:Error state: 1, Severity: 19, Server: TST-PROC22, Line#: 1, msg:SqlDumpExceptionHandler: Process 66 generated fatal exception c0000005EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.I would like to know if we can bulk insert rows into a view thataccesses a table on the remote server using the "bulk insert" or bcpcommand. I tried a small test through SQL Query Analyser to use "bulkinsert" on a such a view.The test that I performed was the following:On database server 1 :create table iqbal (var1 int, var2 int)On database server 2 (remote server):create view iqbal as select var1,var2 from[DBServer1].[SomeDB].[dbo].[iqbal]set xact_abort onbulk insert iqbal from '\MachineIqbaliqbaldata.txt'The bulk insert operation failed with the following error message:[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionCheckForData(CheckforData()).Server: Msg 11, Level 16, State 1, Line 0General network error. Check your network documentation.Connection BrokenThe file iqbaldata.txt contents were :112233If the table that the view references is on the same server then weare able to bulk insert successfully.Is there a way by which I should be able to bulk insert rows into aview that selects from a table on a remote server. If not then couldanyone suggest a workaround. I would actually like to know someworkaround to get the code working using OLEDB. Due to unavoidablereasons I cannot output the records to the file and then use bcp tobulk insert the records in the remote table. I need to have some wayof doing it using OLEDB.Thanks in advanceIqbal
View 7 Replies
View Related
Apr 3, 2006
Fellow database developers,I would like to draw on your experience with views. I have a databasethat includes many views. Sometimes, views contains other views, andthose views in turn may contain views. In fact, I have some views inmy database that are a product of nested views of up to 6 levels deep!The reason we did this was.1. Object-oriented in nature. Makes it easy to work with them.2. Changing an underlying view (adding new fields, removing etc),automatically the higher up views inherit this new information. Thismake maintenance very easy.3. These nested views are only ever used for the reporting side of ourapplication, not for the day-to-day database use by the application.We use Crystal Reports and Crystal is smart enough (can't believe Ijust said that about Crystal) to only pull back the fields that arebeing accessed by the report. In other words, Crystal will issue aSelect field1, field2, field3 from ReportingView Where .... eventhough "ReportingView" contains a long list of fields.Problems I can see.1. Parent views generally use "Select * From childview". This meansthat we have to execute a "sp_refreshview" command against all viewswhenever child views are altered.2. Parent views return a lot of information that isn't necessarilyused.3. Makes it harder to track down exactly where the information iscoming from. You have to drill right through to the child view to seethe raw table joins etc.Does anyone have any comments on this database design? I would love tohear your opinions and tales from the trenches.Best regards,Rod.
View 15 Replies
View Related
Sep 27, 2000
I am doing a ‘Select into’ to make a table from at another table which has as many as 130 millions rows (its well indexed). The new table will most often have about 1000 rows. (During the running of the app, the app will be making many new tables, since hopefully this will be a ‘popular’ item by the users. After the users ‘use’ them, they are dropped.)
I know that locks are held on various system tables (including sysobjects) during this ‘select into’ process. Are they held for the entire process?
What should I be concerned about doing ‘select into’?
Thanks for any help,
Judith
View 2 Replies
View Related
Sep 13, 1999
Hi..,
There r plenty of duplicate rows in the legacy system(Unix /Cobol) from which the data has to be migrated to SQL Server 7.0 Using DTS.Could u please help me in finding out all the repeating rows so that my people could go through it manually and make it unique rows. I expect a query that can be executed to select the repeating rows in a staging/temp table that contain all the rows from legacy system. If u could sujest any other alternative u r most welcome...
Thanks in advance..
View 2 Replies
View Related
Mar 29, 2007
Hi! I am having a problem getting the inserted id from a table.
Here is the table:
/*********************** Companies **********************************/
CREATE TABLE Companies(
CompanyId int IDENTITY(1,1) PRIMARY KEY,
Description nvarchar(30) NOT NULL
)
GO
When I type SELECT @@IDENTITY in SQL Management Studio after a successful insert, it works fine. However, in code (System.Data.SqlServerCe), I get an error using this code:
SqlCeCommand cmd = conn.CreateCommand();
cmd.CommandText = sql;
cmd.CommandType = System.Data.CommandType.Text;
ret = (int)cmd.ExecuteScalar();
I saw a thread with this solution, but do I really have to go to this trouble?
DECLARE @MyTable table
( MyIdentity int )
INSERT INTO Employees ( FirstName, LastName )
OUTPUT inserted.EmployeeID INTO @MyTable
VALUES ( 'Bill', 'Smith' )
SELECT MyIdentity
FROM @MyTable
scope_identity does not seem to work either. Am I missing something?
Thanks!
Michael
View 11 Replies
View Related
Aug 21, 2006
I have created a windows library control that accesses a local sql database
I tried the following strings for connecting
Dim connectionString As String = "Data Source=localhostSQLEXPRESS;Initial Catalog=TimeSheet;Trusted_Connection = true"
Dim connectionString As String = "Data Source=localhostSQLEXPRESS;Initial Catalog=TimeSheet;Integrated Security=SSPI"
I am not running the webpage in a virtual directory but in
C:Inetpubwwwrootusercontrol
and I have a simple index.html that tries to read from an sql db but throws
the error
System.Security.SecurityException: Request for the permission of type 'System.Data.SqlClient.SqlClientPermission, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.
at System.Security.CodeAccessSecurityEngine.Check(Object demand, StackCrawlMark& stackMark, Boolean isPermSet)
at System.Security.PermissionSet.Demand()
at System.Data.Common.DbConnectionOptions.DemandPermission()
at System.Data.SqlClient.SqlConnection.PermissionDemand()
at System.Data.SqlClient.SqlConnectionFactory.PermissionDemand(DbConnection outerConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection,
etc etc
The action that failed was:
Demand
The type of the first permission that failed was:
System.Data.SqlClient.SqlClientPermission
The Zone of the assembly that failed was:
Trusted
I looked into the .net config utility but it says unrestricted and I tried adding it to the trusted internet zones in ie options security
I think that a windows form connecting to a sql database running in a webpage should be simple
to configure what am I missing?
View 28 Replies
View Related