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 RepliesHi all, newbie here (to SQL Server and Visual Studio)...
After a few days of configuring and setting things up, I finally got visual studio 2005 to connect to my sql server 2005 express database. I used server management studio to construct a database with 8 linked tables for my first main application. I plugged in some data and, after setting up proper permissions, was able to get visual studio to connect and return data.
I set up a simple gridview on a page with a sql server connection object to test things out. When I set the connection string to return data from a single table, everything works fine. When I test the connnection within visual studio, I get the results I expect. I build the page and load it up in my browser through the localhost, and I get the expected results.
The problem comes when I try to use a view rather than a table. I build the view in server management studio, and from within visual studio, when I test the connection I get the expected results. However, after building the pages (and rebuilding the site), I get a blank grid in my broswer when I test through localhost. I added another gridview to the page set to a table with a different connection string, and that grid appeared in my browser. I modified that connection string in visual studio to the sql server view, and again, nothing appears in my browser.
I'm not sure if, during all of my tinkering with security and setups I may have inadvertantly shut off the abilty to use views, but I'm not receiving an error messages. I just get a blank page.
Any ideas?Brian
How can I find out a user's suid or login name from within a stored procedure?
View 1 Replies View RelatedI 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,
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
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.
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 RelatedI'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)
{}
}
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
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!
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
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
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
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
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!
Hi All,
I am currently transferring my Access application to SQL Server. Access allows you to declare and use aliases in the query at the same time.
e.g.
Select field1 as Alias1, field2 as Alias2, Alias1 & " " & Alias2 as Alias3 from table1;
In Access the above query will execute perfectly, no problem. However in SQL Server, if you try to run the same query it will give an error "Invalid column name Alias1" meaning that SQL Server is searching for Alias1 as a field in the table, not as an alias from the query.
My question is does SQL Server have a facility to declare and use alias directly as in Access and if no, is there a workaround?
Thanks for your time.
Regards:
Prathmesh
Hi there,
I'm new to SQL Server please help me, i want to create identity column(AutoNumber) when creating views.
My client want me to update a table and the view with a new column which should be hidden. I am kind of confused on how to go about this in order to get a good result. PLEASE HELP!!
View 1 Replies View RelatedI am creating a view for the table:
bellus=# select * from host_application_definition;
id | type_value | connection_value | group_value | application_value | host
----+------------+------------------+-------------+-------------------+----
From the table meta_host_types;
id | value | types | name
----+-------+-----------+--------
1 | agm | host-type | Rencid
I would like to seperate value into type_value and connection_value, because it holds both values.
Any tip?
bellus=# d host_application_definition;
Table "public.host_application_definition"
Column | Type | Modifiers
-------------------+---------+--------------------------------------------------------------------------
id | integer | not null default nextval('host_application_definition_id_seq'::regclass)
type_value | integer |
connection_value | integer |
group_value | integer |
application_value | integer |
host | integer |
Indexes:
"host_application_definition_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
"host_application_definition_application_value_fkey" FOREIGN KEY (application_value) REFERENCES appsmarteye(id)
"host_application_definition_connection_value_fkey" FOREIGN KEY (connection_value) REFERENCES meta_host_types(id)
"host_application_definition_group_value_fkey" FOREIGN KEY (group_value) REFERENCES meta_host_grouptypes(id)
"host_application_definition_host_fkey" FOREIGN KEY (host) REFERENCES master_hosts(id)
"host_application_definition_type_value_fkey" FOREIGN KEY (type_value) REFERENCES meta_host_types(id)
d meta_host_types;
Table "public.meta_host_types"
Column | Type | Modifiers
-------------+---------+---------------------------------------------------------------------
id | integer | not null default nextval('meta_types_application_id_seq'::regclass)
application | integer |
value | text |
comments | text |
types | text |
Indexes:
"meta_types_application_pkey" PRIMARY KEY, btree (id)
"meta_host_types_id_key" UNIQUE, btree (id)
"meta_host_types_value_key" UNIQUE, btree (value, application)
"meta_host_types_value_key1" UNIQUE, btree (value)
Foreign-key constraints:
"meta_types_application_application_fkey" FOREIGN KEY (application) REFERENCES appsmarteye(id)
Let me explain in simplified terms. We have an ETL process:
1) download data from the iSeries table Cust into SQL table Cust.
2) Tran_Cust is a view created FROM Cust
3) Copy from view Tran_Cust to table ods_Cust
4) v_ods_Cust is a view created from ods_Cust
I want to look for inconsistencies in the column definitions as a column moves from iSeries Cust, SQL Cust, view Tran_Cust, ods_Cust, view v_ods_Cust. The columns generally keep the same names except for v_ods_cust. Here a column like CustName will be created with a more meaningful column name - "CustName as 'Customer Name'.
We have hundreds of tables and we have column definition inconsistences where the host changed their column defintion and we made no changes on the SQL side. Or someone made a SQL column change in one table, but not both tables and the views were not dropped and recreated.
I have the list of tables. Think that I can put together some tsql to loop through the list of tables and create a new table based on the iSeries definition, then compare columns defintion across all the tables and views - except for the v_ods. THis is where columns are given new names - such as "CustName as 'Customer Name'". Need the actual view definition to find that CustName is 'Customer Name' and then compare the definition of ods_Cust.CustName to v_ods_Cust.[Customer Name]
How can I find the view definiton of say v_ods_Cust, seperate out the source column names (CustName) and destination column names (Customer Name) so that I can compare the source column names to another table and fine the differences?
I want to add $ symbol to column values and convert the column values to western number system
Column values
Dollar
4255
25454
467834
Expected Output:
$ 4,255
$ 25,454
$ 467,834
My Query:
select ID, MAX(Date) Date, SUM(Cost) Dollars, MAX(Funded) Funding from Application
COST is the int datatype and needs to be changed.
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 RelatedI am trying a create views that would join 2 tables:
Table 1: Has all the columns need by a view (
Name: Product
Structure: ID, Attribute 1, Attribute 2, Attribute 3, Attribute 4, Attribute 5 etc
Table 2: Is a lookup table that provides the names of columns
Name: lookupTable
Structure: tableName, ColumnName, columnValue
Values: Product, Attribute1, Color
Product, Attribute2, Size
Product, Attribute3, Flavor
Product, Attribute4, Shape
I want to create a view that looks like
ID, Color, Size, Flavor, Shape
I have generated a .dsv using named queries.
The dsv consists of 3 tables:
1)CLIENT
2)client_guarantor.
3)client_contacts.
The client_guarantor and client_contacts tables both have a foreign key into the CLIENT table via the column CLIENT.client_id. To give you a visual, the .dsv design view would look something like this:
client_guarantor <- CLIENT -> client_contacts.
I use this .dsv to generate a model and deploy it. My problem arises once I get into report builder. It will not let me build a report that uses columns from all three tables. For instance, if i pull a column from the CLIENT table, then a column from the client_contacts table, and next try to get anything from the client_guarantor table, report builder lets me navigate back to the client_guarantor table (because i have added a column from CLIENT) but wont let me add any columns from client_guarantor to the report.
Am i missing something? I assume that since the CLIENT table can see both the client_guarantor and client_contacts tables, if I add anything from the CLIENT table into my report, I should have access to both other tables located in my model. Am I wrong in this assumption, or do I need to add some flag to my model or something??? Any help or insight is appreciated.
In the data flow, I have an "OLE DB source" container that calls an SP. The SP does a select statement at the end. If I hit the "Preview" button I'm seeing the results come back, however, when I go to the columns I'm not seeing anything. I don't see the column name. Is there any reason why I don't seem to see this column coming back?
Thanks,
Phil
Parameters do not appear when I jump to a linked report. What causes the parameters not to appear? I did not make them hidden, so why do they not appear? I can see them when I am within visual studio, but not when the report is published to the web?
View 3 Replies View RelatedSecond post for the day. I've just noticed a few indexes in our tables that have are named like such: hind_c_207_1. They seem to have been put in automatically, and are messing with our index structure. Anyone know where these come from and how I can stop them? Any help would be great.
View 2 Replies View RelatedGreetings.I am trying to add some debug to a stored procedure.I put some statements like this throughout the code:select 'some message here';but I never see them.I put them in a test procedure that works fine, so that I have a feeling thatwhen an insert fails in a down-stream try catch block, it messes up thedisplay of the information.Can someone tell me how to best go about outputting debug I can see from astored procedure, or if I am even correct about try catch blocks throwingerrors, messing up the debug visibility.I can post code if necessary. Just a general debug question here.thanksJeff Kish
View 2 Replies View RelatedHi,We have a SQL server db backend for our ERP system. I have written aCrystal Report to extract data from one particular table calleditemspecbomw (c.30000 records) which contains amongst other thingsBill of Materials costings. When I run the report I know that somerows are missing as when I look at values through the ERP systemitself, the values are different.What I have found is that when I run the equivalent ERP system report,the Crystal Report I have written shows extra rows. SQL Query Analyserbehaves exactly the same as the report I have written even when usinga "select * from". I have tested opening up a bigger tables (c. 700000records) which appears without a problem. If anyone knows why thismystery is happening, I woould be grateful for your help.Many thanks,Tony.
View 2 Replies View RelatedHello,
I am trying make some imports and I am using the wizard.
I have created a new project and added 2 new datasources. One for a DB2 database and one for a SQL Server database
I select Project | SS Import and Export Wizard and the wizard launches.
When I look under datasource I can't find my 2 created datasources. I can create new ones but this is not something I want.
Any help ?
Constantijn Enders
i have a list which is hidden at the top of my body and then some text boxes afterwards...this should all fit on the first page, but as soon as one of my textboxes has an expression the rest of the data skips to the second page
View 3 Replies View Related
I have setup a merge replication publication (SQL server 2005 wth SQL Mobile) with dynamic filters based on host name and when i sync to the pda all data appears fine. When I make changes on the PDA and synchronise, the changes are pushed back to the server fine.
However when I change data on the server and synchronise at the PDA, no changes ever appear!
Surely I must have missed something obvious or done something really stupid.
If i check the replication monitor it just says no changes so any suggestions would be great.
Thanks,
Andy
A couple of databases on one of our SQL Servers is missing in the dbdropdown in query analyzer.Does anyone know what I need to update to sync this with the actualdatabases?Thanks,Burt
View 1 Replies View Related